Results 1 to 15 of 15
  1. #1
    Join Date
    Feb 2004
    Posts
    107

    Question Unanswered: Problem with Dynamic SQL !!!

    Hi friends,

    I have a procedure with an input parameter & output parameter. The input parameter value is a table name which has an identity column. The procedure will set the next value of the identity column to the output parameter. I stuck with the dynamic sql. Here it goes....

    ----------------------------------------------------------------------------------------------------------------------------------
    CREATE TABLE seqtest(nextVal NUMERIC(38) IDENTITY(1000000,1),dummyCol TINYINT);

    create procedure NextVal (@seqName varchar(20), @nextVal int OUTPUT)
    AS
    BEGIN
    DECLARE @nv int
    EXECUTE( 'DELETE from ' + @seqName)
    EXECUTE( 'INSERT INTO ' + @seqName + '(dummyCol) VALUES(0)' )
    EXECUTE( 'SELECT '+ @nv +' = id from ' + @seqName )
    SET @nextVal = @nv
    END

    ----------------------------------------------------------------------------------------------------------------------------------
    after i created the table & procedure, i executed the below code:
    ----------------------------------------------------------------------------------------------------------------------------------
    DECLARE @nextVal1 int
    EXECUTE NextVal 'seqtest', @nextVal = @nextVal1 OUTPUT
    print @nextVal1
    ----------------------------------------------------------------------------------------------------------------------------------

    but it says

    Msg 170, Level 15, State 1, Server SWISSQL-WIN2K, Line 1
    Line 1: Incorrect syntax near '='.
    (return status = 0)

    Can anyone point out where i went wrong???

    Jake

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    DECLARE @nextVal1 int
    EXECUTE NextVal @seqName='seqtest', @nextVal = @nextVal1 OUTPUT
    print @nextVal1
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Feb 2004
    Posts
    107
    Hi Eniqma,

    It didn't solve my prob. same error...
    The problem is in the select statement
    EXECUTE( 'SELECT '+ @nv +' = nextVal from ' + @seqName )
    It says Incorrect syntax near '='.

    any idea???

    Jake

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Oops ... i forgot ... you cannot create a dynamic string inside an execute statement

    You will have to do something like
    Code:
    create procedure NextVal (@seqName varchar(20), @nextVal int OUTPUT)
    AS
    BEGIN
    DECLARE @nv int,@query varchar (300)
    select @query = 'DELETE from ' + @seqName
    EXECUTE(@query )
    select @query = 'INSERT INTO ' + @seqName + '(dummyCol) VALUES(0)' 
    EXECUTE(@query )
    select @query ='SELECT '+ @nv +' = id from ' + @seqName 
    EXECUTE(@query )
    SET @nextVal = @nv
    END
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  5. #5
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    alter procedure NextVal (@seqName varchar(20),@nextval int output)
    AS
    BEGIN
    DECLARE @nv int,@query varchar (300)
    select @query = 'DELETE from ' + @seqName
    EXECUTE(@query )
    select @query = 'INSERT INTO ' + @seqName + '(dummyCol) VALUES(0)'
    EXECUTE(@query )


    select @nextval= @@identity


    END


    DECLARE @nextVal1 int
    EXECUTE NextVal @seqName='seqtest', @nextVal = @nextVal1 OUTPUT
    print @nextVal1

  6. #6
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Code:
    alter procedure NextVal (@seqName varchar(20),@nextval int output)
    AS
    BEGIN
    DECLARE @nv int,@query varchar (300)
    select @query = 'DELETE from ' + @seqName
    EXECUTE(@query )
    select @query = 'INSERT INTO ' + @seqName + '(dummyCol) VALUES(0)' 
    EXECUTE(@query )
    
    
    select @nextval= scope_identity()
    
    END
    
    
    DECLARE @nextVal1 int
    EXECUTE NextVal @seqName='seqtest', @nextVal = @nextVal1 OUTPUT
    print @nextVal1
    You should really be using scope_identity()

    And from what I think what you are trying to achieve , it would not matter even if you used a identity column in your original table ...
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  7. #7
    Join Date
    Feb 2004
    Posts
    107

    Thumbs up

    Hi eniqma, when the procedure is executed, it will say

    Msg 245, Level 16, State 1, Server SWISSQL-WIN2K, Line 9
    Syntax error converting the varchar value 'SELECT ' to a column of data type int.

    as int is concatenated with string.

    @@IDENTITY approach guides me to the solution. but i didn't use @@IDENTITY as it returns the last identity value generated for any table in the current session, across all scopes.
    I used IDENT_CURRENT('table_name') as it returns the last identity value generated for a specific table in any session and any scope.

    so here is the final procedure

    ----------------------------------------------------------------------------------------------------------------------------------
    alter procedure NextVal (@seqName varchar(20), @nextVal int OUTPUT)
    AS
    BEGIN
    EXECUTE( 'DELETE from ' + @seqName)
    EXECUTE( 'INSERT INTO ' + @seqName + '(dummyCol) VALUES(0)' )
    SET @nextVal = IDENT_CURRENT(@seqName)
    END
    ----------------------------------------------------------------------------------------------------------------------------------

    thanks eniqma & harshal for your time & help.

    Jake

  8. #8
    Join Date
    Feb 2004
    Posts
    107
    hi enigma, SCOPE_IDENTITY didn't return the last inserted value if the insert statement is executed as dynammic SQL, whereas IDENT_CURRENT returns correctly.

    But the doc says, SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

    any clue why it didn't work???

    Jake

  9. #9
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    Quote Originally Posted by Jake K
    hi enigma, SCOPE_IDENTITY didn't return the last inserted value if the insert statement is executed as dynammic SQL, whereas IDENT_CURRENT returns correctly.

    But the doc says, SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

    any clue why it didn't work???

    Jake

    I think u have already answered the question.

    But the doc says, SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

    since the select and insert are not in the scope i think it wont work.

  10. #10
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Hmm ... you are right harshal ... never thought about that

    Jake .. mind clarifying why you are going through all this when you could have done with a indentity column in the table for which you are generating a sequence ...
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  11. #11
    Join Date
    Feb 2004
    Posts
    107
    i thought the dynamic sql execution would happen in the current scope. it puzzles me... so what actually happens is dynamic sql execution happens in a different scope than where it is called. may be i have to understand the execute statement further...

    thanks for the clarification, harshal.

    Jake

  12. #12
    Join Date
    Feb 2004
    Posts
    107
    oh! sorry, i missed that.... i am trying to automate the SQL stored procedures conversion from Oracle to SQL Server. as you know Oracle has sequence & SQL Server doesn't.. That's why i trying to simulate sequence in SQL Server...
    Thanks for your interest.

    Jake

  13. #13
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Oh ... so thats what you are trying to do ...

    Talking about scope ... its the same in sql as in other languages ...
    If you called a stored procedure B inside a stored procedure A then the variables declared in sproc B get out of scope as soon as it returns control to sproc A. Similar with EXEC ... think of it as a stored procedure which executes what ever is passed to it and returning the result.
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  14. #14
    Join Date
    Feb 2004
    Posts
    107
    now i understand, enigma... glad to see your reply.

    Thanks,
    Jake

  15. #15
    Join Date
    Oct 2004
    Posts
    6

    Ints into strings

    Without reading this little lot too deeply, the answer to the original question is:
    1: Look at the spaces before and after you + signs '+ @n +' needs to be:
    ' + @n + '
    2: You have to explicilty convert the int variable to a string (sorry, thats the vb in me coming out) varchar before you can add it to one.

    E.G. 'I am a varchar ' + CAST(@IntVariable AS Varchar(250)) + ' The rest of the varchar string'

    Otherwise you get the converting int to varchar error.

    Have fun
    Best regards
    Steve

    P.S. Don't forget the spaces when breaking strings and inserting variables (before and after). Use Print CAST(@SqlString as Varchar(250)) to check your Sequel statement for errors.
    Last edited by Dynamic SQL; 10-29-04 at 10:22.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •