May 8, 2002 at 10:34 am
In SQL Server 2000 I'm using a stored procedure to add a new record to a table who's PK is an auto generated identy field. Once I commit the transaction how can I get the new PK to return as an output parameter?
May 8, 2002 at 10:55 am
use system variable @@IDENTITY
Returns the last-inserted identity value.
Return Types
numeric
After an INSERT, SELECT INTO, or bulk copy statement completes, @@IDENTITY contains the last identity value generated by the statement. If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL. If multiple rows are inserted, generating multiple identity values, @@IDENTITY returns the last identity value generated. If the statement fires one or more triggers that perform inserts that generate identity values, calling @@IDENTITY immediately after the statement returns the last identity value generated by the triggers. The @@IDENTITY value does not revert to a previous setting if the INSERT or SELECT INTO statement or bulk copy fails, or if the transaction is rolled back.
Examples
This example inserts a row into a table with an identity column and uses @@IDENTITY to display the identity value used in the new row.
INSERT INTO jobs (job_desc,min_lvl,max_lvl)
VALUES ('Accountant',12,125)
SELECT @@IDENTITY AS 'Identity'
May 8, 2002 at 3:16 pm
Check out the following thread http://qa.sqlservercentral.com/forum/topic.asp?TOPIC_ID=2903&FORUM_ID=8&CAT_ID=1&Topic_Title=Get%20the%20value%20of%20an%20autoincrement%20identity%20col&Forum_Title=T-SQL Andy makes mention of Scope_Identity() function for SQL 2000 as a better option and other ideas are given.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 8, 2002 at 3:22 pm
Thanks! Yes, I read in the books online that @@Identity was quite problematic. I was using IDENT_CURRENT but I see that SCOPE_IDENTITY is the better choice.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply