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.
-return-the-id-from-the-database-on-insert.aspx)