SCOPE_IDENTITY() return the id from the database on insert

As a .NET programmer most of my time is spent coding in C# and I try to avoid writing SQL where possible. Why is it so complicated to write a loop without turning the database upside down? I'm not saying it's not possible it's just the language can me annoying at times.

Anyways, Recently I had to write an insert stored procedure and needed to return the ID of the row I was inserting. While writing my usual bad SQL I came across a fascinating function I’ve never used before, SCOPE_IDENTITY(). 

Here's short example of how you can use the SCOPE_IDENTITY() function while doing an SQL INSERT to return the identity value of the row just inserted.

In this example I’m inserting a new customer record using a stored procedure and have declared a output parameter which I'd like to return to my application.

CREATE PROCEDURE [dbo].[Customer_Insert]

@Name VARCHAR(255),

@Email VARCHAR(255),

@Phone VARCHAR(255),

@CustomerID INT OUTPUT

AS

BEGIN

INSERT INTO dbo.Customer ([Name], Email, Phone)

VALUES (@Name,@Email,@Phone)

SET @CustomerID = SELECT CustomerID 

FROM dbo.Customer 

WHERE [Name] = @Name 

AND Email = @Email 

AND Phone = @Phone

END

What I’ve done wrong here is after inserting run a select to try and SET the @CustomerID. This is a bit risky as it could return more than 1 record, or it return an of completely different record. 

SQL's SCOPE_IDENTITY() function can return the last identity value inserted into an identity column in the same scope e.g. a stored procedure, trigger, function, or batch. Alternativly you can also use the @@IDENTITY function to return the last identity value inserted into an identity column regardless of the scope.

Now lets improve my stored procedure using SCOPE_IDENTITY().

CREATE PROCEDURE [dbo].[Customer_Insert]

@Name VARCHAR(255),

@Email VARCHAR(255),

@Phone VARCHAR(255),

@CustomerID INT OUTPUT

AS

BEGIN

INSERT INTO dbo.Customer ([Name], Email, Phone)

VALUES (@Name,@Email,@Phone)

SET @CustomerID = CAST(SCOPE_IDENTITY() AS INT)

END

I've casted the value returned by SCOPE_IDENTITY() to make sure the confirms to the return parametor @CustomerID INT OUTPUT.