@@IDENTITY

  • Have hit a bit of stumbler with respect to @@IDENTITY.

    After an insert into a table I always want to get the @@IDENTITY of the row just inserted so that I can return it to the user. However, this one particular table has a Trigger on it which creates an entry into another table. When I access the @@IDENTITY immediately after the INSERT I get the @@IDENTITY of the row inserted on the second table and not the first.

    Is there a way around this?

    Here is the code I am using :-

    -- #

    -- ### Insert into table

    -- #

    INSERT INTO dbo.tbl_job_raw

    (

    field1

    ,field2

    )

    VALUES

    (

    @l_parm_field1

    ,@l_parm_field2

    )

    -- #

    -- ### Store the @@ERROR and @@ROWCOUNT variables before they are changed

    -- #

    SELECT @l_SQL_ERROR= @@ERROR, @l_SQL_ROWCOUNT = @@ROWCOUNT

    -- #

    -- ### Test error condition

    -- #

    IF @l_SQL_ERROR <> 0

    BEGIN

    SELECT @l_Error_Code = @lk_ERROR_SEED + 3

    SELECT @s_Error_Msg = "Error Code " + STR(@l_SQL_ERROR) + " encountered during Insert"

    END

    ELSE

    BEGIN

    -- #

    -- ### Set the returned row identifier number

    -- #

    SELECT @l_parm_output_row_id= @@IDENTITY

    END

  • Try using IDENT_CURRENT

    Have a look in BOL. A few examples included.

    Crispin

    Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

  • Thanks for assistance.

    IDENT_CURRENT seemed to do the trick. Although I was forced to enter the name of the table.

    Help much appreciated.

  • Watch out for ident_current though, as it returns the latest identity-value on a table, from any connection. So if you're connection inserts a value, then a second connection inserts a value before you manage to run ident_current you will get the second value. Use scope_ident as Andy suggested instead.

    --

    Chris Hedgate @ Apptus Technologies (http://www.apptus.se)

    http://www.sql.nu

  • Use SCOPE_IDENTITY in this case. There's a reason it's called that - because it retrieves the ID within the scope of your proc. IDENT_CURRENT should only be used to obtain the ID of the most recent entry in a table (not necessarliy from your proc's insert).

    keith


    keith

  • SCOPE_IDENTITY it is then.

    Once again, many thanks for contributions.

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

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