November 5, 2004 at 9:15 am
Hi,
I'm writing a stored procedure that starts off by inserting into a table. Once thats done the inserted Data will have a unique id which is generated by sqlserver. I need to return that id after the insert so as to use it in other tables in the next lot of inserts. How can I do this? Any helps aappreciated
Thanks again
Matt
November 5, 2004 at 10:46 am
What you are looking for is:
select @@identity
November 5, 2004 at 11:34 am
If you're using SQL Server 2000, I recommend that you use SCOPE_IDENTITY() instead of @@IDENTITY. The latter will return the last identity generated for your session, whereas the former will return only the last identity in your current scope. What this means is that if a trigger was fired due to your insert which caused generation of an identity on a different table, @@IDENTITY will return that value to you. SCOPE_IDENTITY() will return the value from the table you actually inserted into.
--
Adam Machanic
whoisactive
November 5, 2004 at 12:31 pm
I did not know that Adam. Thanks for the info. I'll pass that on to my developers.
November 6, 2004 at 2:04 pm
And in case you need it for your developers in written what Adam said, have a look at BOL for "IDENT_CURRENT". Also contains a nice example about the differences between each approach.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
November 8, 2004 at 1:38 am
Many thanks
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply