Post reply

procedure for returning key value

  • July 13, 2005 at 3:41 am

    #93514

    I want to get a stored procedure to pass back the key of the table it

    has just inserted into. So I've got the insert statement followed by a

    set & select @@identity statement to get the key. As raw SQL it works

    but as a stored procedure, it passes back nulls. I've searched the

    internet for clues but found none.

  • July 13, 2005 at 5:27 am

    #573820

    I would research @@IDENTITY/SCOPE_IDENTITY() in BOL.  Partial from BOL

    SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]

    GO

    SELECT   @@IDENTITY AS [@@IDENTITY]

    GO

    --Here is the result set.

    SCOPE_IDENTITY

    4

    /*SCOPE_IDENTITY returned the last identity value in the same scope, which was the insert on table TZ*/

    @@IDENTITY

    115

    /*@@IDENTITY returned the last identity value inserted to TY by the trigger, which fired due to an earlier insert on TZ*/



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • July 14, 2005 at 12:32 am

    #574265

    how about something like this?

     

    CREATE TABLE #nd_test (col int identity, nd char(3))

    CREATE proc sP_nd_test (@identity int OUT)

    As

    insert into #nd_test (nd)

    select 'hello'

    set @identity = @@identity

    GO

    DECLARE @my_test int

    exec sP_nd_test @my_test OUT

    select @my_test

  • July 14, 2005 at 6:46 am

    #574337

    You mean set @identity = SCOPE_IDENTITY()??

  • July 14, 2005 at 8:41 am

    #574428

    in a stored proc you have to use scope_identity()

    See SCOPE_IDENTITY in BOL with a goodd explanantion



    Bye
    Gabor

  • July 14, 2005 at 6:20 pm

    #574630

    correct it's more safer using scope_identity()

    Thanks.

     

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply