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, which is great as we have dba's that can help with the more complicated queries. 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.