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.