Return the value of @@IDENTITY - best way?
-
Hi,
Without using an output parameter, what is the best way to return @@IDENTITY from a stored procedure? I have simply stated :-
RETURN @@IDENTITY
and it seems to work perfectly well. However, I am not sure this is a "best practice" as usually, a sprocs return value would generally indicate a success or status condition as opposed to a "function-like" scalar return value.
Any advice would be appreciated.
Thanks!
-
I usually DECLARE a variable for example,
DECLARE @Identity INT
SET @Identity = SCOPE_IDENTITY()
SELECT @Identity
It's safer to use SCOPE_IDENTITY() as oppossed to @@Identity because SCOPE_IDENTITY runs within it's own scope and you will not have the possibility of returning the incorrect identity value.
-
Thanks for this advice - it has really helped!
-
Depending on circumstances and exactly how you want to use it, there are three different things you should look at.
@@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT. All three are described in detail in BOL.
/Kenneth
-
The main issue is not how to identify the last PK value but how to return it to the calling procedure without using an output parameter.
Thanks.
-
Then you already know that using @@IDENTITY in the presence of triggers is "death by SQL" and that the use of "it" is hardly ever the "best way" (as Ken was trying to imply)...
...just making sure.--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
< vbg > Actually I'm missing Kenneth's usual 'It depends...' comment.
IIRC, read IDENT_CURRENT in BOL. There's an example of when to use what.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Ouch, sorry Frank, I forgot
(editing 'Depending on circumstances' to 'It depends')
/Kenneth
-
Hi Jeff
>Then you already know that using @@IDENTITY in the presence of triggers is "death by SQL"
Nope, I didn't know that. Care to elaborate?
Thanks.
-
It's all explained in BOL, just look for '@@IDENTITY'.
/Kenneth
-
I used scope_identity once and it diddnt work. I was sending 100's of sql statements up to the server using ADO from VB like this (forgive the pseudo code):
dim SQL as string
SQL = "INSERT a,b,c,d into table A;SELECT @id = SCOPE_IDENTITY;INSERT @id, e,f,g,h into table B;INSERT a,b,c,d into table A;SELECT @id = SCOPE_IDENTITY;INSERT @id, e,f,g,h into table B;INSERT a,b,c,d into table A;SELECT @id = SCOPE_IDENTITY;INSERT @id, e,f,g,h into table B;INSERT a,b,c,d into table A;SELECT @id = SCOPE_IDENTITY;INSERT @id, e,f,g,h into table B;INSERT a,b,c,d into table A;SELECT @id = SCOPE_IDENTITY;INSERT @id, e,f,g,h into table B;INSERT a,b,c,d into table A;SELECT @id = SCOPE_IDENTITY;INSERT @id, e,f,g,h into table B"
ado.Execute SQL
For some reason @@Identity returned the correct id for the new record, but SCOPE_IDENTITY always returned 0 (or the wrong #, I cant remember).
So its not quite as simple as SCOPE_IDENTITY. I don't know the answer.
-
A look at the BOL chapters that have been mentioned here, should help you figuring out, what you did wrong with SCOPE_IDENTITY() and why this didn't work for you.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Gazley,
The reason I said that using @@IDENTITY in the presence of triggers is "Death by SQL" is because of the way @@IDENTITY works... it (basically) returns the IDENTITY of the last row updated or inserted of ANY TABLE. If you have triggers that write to other tables, such as Audit Logs, and those triggers are "After" triggers (most are), then you will get the IDENTITY of the last row to be inserted/updated from the tables updated by the trigger(s) and not the table you were expecting it to come from. As you can imagine, that would create some rather large problems in the database.
SCOPE_IDENTITY() was introduced in SQL Server 2000 and overcomes the problem outlined above. It will always return the IDENTITY from the correct table because it is "limited in scope" to the object-table of the update/insert.
While it is true that @@IDENTITY and SCOPE_IDENTITY() function in a nearly identical fashion when no triggers containing writes to other tables are present, why take the chance on a trigger being added in the future? Use SCOPE_IDENTITY() in those places where you want the IDENTITY to be returned from the object-table of the update/insert.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
John,
SCOPE_IDENTITY() does require the use of parentheses like GETDATE() does. I didn't see them in the code you posted and I'm hoping that was the only problem you had.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi Jeff
Thanks for your reply, which makes the position perfectly clear to me now. I really appreciate you taking the time and trouble to respond in this way.
Best regads,
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply