Return id of last inserted row from stored procedure

Question

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

A simplified version of my code is as follows:

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

I call this stored procedure through Execute Scalar in Enterprise library 4.1.

It returns null. Does anyone see what I'm doing wrong.

Answer 1

I would say that you should use SCOPE_IDENTITY() , because @@identity will return the identity of the last thing inserted (which might not be your stored procedure if multiple requests are running at the same time).

You also need to select it, not RETURN it.

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

So...

 SELECT SCOPE_IDENTITY(); 

probably more than what you want.

Answer 2

You should use select, not return, but you should also use SCOPE_IDENTITY to prevent incorrect identification errors, @@ IDENTITY is not limited to a specific area.

 SELECT SCOPE_IDENTITY() 

More detailed information can be found here:

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

Answer 3

you must use select @@identity