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.

Answer 1

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.

Answer 2

you should use select @@identity

Answer 3

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