sql server get identity after insert stored procedure

In this step by step SQL Tutorial I will show you how to using SQL Server get identity after insert using stored procedure. For the purpose of this tutorial I will use SQL Server 2008 R2.

Other Versions of SQL Server:

• SQL Server 2012
  • The examples we used should work in SQL Server 2012
• SQL Server 2008 / R2
  • This blog post has been written using SQL Server 2008 R2
• SQL Server 2005
  • We suspect that this will work in SQL Server 2005 as well

NOTE: The method we will use contains some bugs which we will mention so ensure you fully test your code and make appropriate research. 

Who is it for?

• People who need to insert row and get IDENTITY value back

Contents

• Introduction to get Identity
• How to get identify after Insert
• Get Identity after insert in stored procedure

Introduction to get Identity

There are different way to get identity ID after insert and there are:

NOTE: Both @@identity and Scope_identity contained a bug that apparently was fixed certain version. For more info visit http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=328811

• @@IDENTITY - Returns last inserted identity using current connection. This means that you get last inserted identity which might not be necessarily be after your insert. If you have trigger that insert row and new identity inserted you might get this ID instead. Methpd below seems to be more reliable and @@identity should be avoided.
• SCOPE_IDENTITY() - Returns last identity value that was created in current connection but also the statement scope. This means it should return Identify of your statement (not affected by triggers). I will use this method in my example. 
• IDENT_CURRENT(‘tablename’) - Returns last inserted Identity value in a specifed table. Regardless of the conneciton and should be used only in very specific scenarios.
• OUTPUT INTO - This clause can be used with INSERT and you can ouput inserted rows (with Identity) to another table or table variable and access the value. This sounds like the most reliable way to get exactly the ID you want, however as we don't know the limitation of this method (if any) and extra code that is required in our case we will just use SCOPE_IDENTITY.

How to get identify after Insert

-- First let me create Test table

CREATE TABLE

dbo.Test (

ID INT IDENTITY(1,1) NOT NULL

,TestField NVARCHAR(50) NOT NULL

)

GO

-- Below I insert one row into my Test table. This will generate new ID

INSERT INTO dbo.Test (TestField)

VALUES ('test')

-- and here's how you can get the identity back from the last inserted ID

SELECT SCOPE_IDENTITY()

Get Identity after insert in stored procedure

-- below is stored procedure T-SQL Script

CREATE PROCEDURE dbo.GetIdentityAfterInsert

@ID AS INT OUTPUT

AS

BEGIN

--insert new row

INSERT INTO dbo.Test (TestField)

VALUES ('test');

--save identity value and return using stored procedure OUTPUT parameter

SET @ID = (SELECT SCOPE_IDENTITY())

END

GO

How it works?  The stored procedure accepts one parameter @ID (this actually should always be empty when you use SP) but it uses OUTPUT to return result.

So you can capture Identity can store it in variable using the following code

DECLARE @ID AS INT

EXEC dbo.GetIdentityAfterInsert @ID OUTPUT

SELECT @ID

NOTE: OUTPUT in the middle line is very important. If you don't put it than the variable won't be populated.

I hope this article will help you if your code and give you options that you can use to get identity value after insert either using T-SQL or stored procedure.

Take care

Emil