Return identity of last inserted row from stored procedure

Question

I am trying to return identity of last inserted row from a stored procedure.

A simplified version of my code looks like this:

CREATE PROCEDURE [sp_name]
    @AuthorisationCode uniqueidentifier
AS
    INSERT INTO [tablename]
           ([AuthorisationCode]
           )
     VALUES
           (@AuthorisationCode
           )

 RETURN @@IDENTITY

GO

I am calling this stored procedure via Execute Scalar in Enterprise library 4.1.

It returns null. Anybody see what I am doing wrong.

As an aside, it's best not to name procs with a sp_ prefix. But maybe that's psuedocode anyway. — Mark SQLDev, Nov 30 '10 at 15:55
**RETURNING** values is usually reserved for error handling (e.g. anything other than 0 means there was an error). — Brad, Nov 30 '10 at 15:58
@Mark, yes, it **CAN** mean anything you want; that's why I said it's "usually reserved". — Brad, Nov 30 '10 at 16:56

score 13 · Accepted Answer · answered Nov 30 '10 at 15:56

I'd say you should be using SCOPE_IDENTITY() as @@identity will return the identity of the last thing inserted (which may not be your stored procedure if multiple queries are running simultaneously).

You also need to SELECT it, not RETURN it.

ExecuteScalar will return the first column value from the first row of a result set.

So...

SELECT SCOPE_IDENTITY();

is probably more what you want.

score 7 · Answer 2 · answered Nov 30 '10 at 15:55

7

You should use select rather than return, but you should also use SCOPE_IDENTITY to prevent issues with the wrong identity being returned, @@IDENTITY is not limited to a specific scope.

SELECT SCOPE_IDENTITY()

More information can be found here:

http://msdn.microsoft.com/en-us/library/ms190315.aspx

answered Nov 30 '10 at 15:55

MrEyes

13,059
10
48
68

+1 wish I could vote more than once; this is such an under-utilized keyword – Brad Nov 30 '10 at 15:56

score 0 · Answer 3 · answered Nov 30 '10 at 15:53

0

you should use select @@identity

answered Nov 30 '10 at 15:53

serhat_pehlivanoglu

982
8
13

2

I don't want to downvote you, but you should really use `SCOPE_IDENTITY` instead of `@@identity` (see MrEyes answer) – Brad Nov 30 '10 at 15:57
@@identity is probably fine in this case. Understanding the difference between current identity, scope identity, and @@identity is important, but @@identity isn't always wrong. What if there's a trigger which for some ungodly reason is changing the identity at insert time and you want to know the last identity inserted? Then you'd need @@identity and scope_identity won't work. – D. Patrick Nov 30 '10 at 16:01
yes Brad, that is true , using scope_identity is the better choice here. (well, not the better choice but, depends on your need i guess). Althouhg, at my answer, i was referring to the usage of "return". And downvoting is the right choice here, it is an important difference between the two. – serhat_pehlivanoglu Nov 30 '10 at 16:05
@Patrick is right: `@@identity` is not always wrong. @futile, I don't think downvoting is the right choice when you clearly understood what would solved the OP's immediate problem: `SELECTING` the result when using `ExecuteScalar` – Brad Nov 30 '10 at 16:59

Return identity of last inserted row from stored procedure

3 Answers3

Linked