How to retrieve the identity value when inserting a record into a Microsoft SQL Server table

A common requirement when inserting a record into a table that contains an identity column is to return the identity value of the newly inserted record. A common mistake is to use @@IDENTITY or IDENT_CURRENT, both of which could return an unexpected value under some circumstances. This document describes a technique to use SCOPE_IDENTITY which does not suffer the drawbacks of the other two methods.

Why @@IDENTITY and IDENT_CURRENT should not be used

Using @@IDENTITY after an insert will return the last-inserted identity value. However, this might be an unexpected value if, for example, the inserted record fires triggers to perform additional inserts. In this case, calling @@IDENTITY immediately after the statement will return the last identity value generated by the triggers.

Using IDENT_CURRENT after an insert will return the last-inserted identity value for a specified table. However, this might be an unexpected value if another insert on the table is performed between the time of the initial insert and the calling of IDENT_CURRENT.

How and why to use SCOPE_IDENTITY

Using SCOPE_IDENTITY avoids the drawbacks of using @@IDENTITY and IDENT_CURRENT.Note however that SCOPE_IDENTITY is only available with Microsoft SQL Server 2000 or later.The technique for using SCOPE_IDENTITY is to call it immediately after the INSERT statement within a stored procedure. The following example shows a stored procedure called InsertCases that uses this technique to return the identity value for the CaseID identity column of the inserted record. This example has two columns, in addition to the CaseID identity column, called CaseName and CaseDescription. Notice that the @CaseName and @CaseDescription are input parameters but the @CaseID parameter is an output parameter used to return the identity value on exit from the stored procedure.

CREATE PROCEDURE [dbo].[InsertCases] @CaseID int output, @CaseName nvarchar(255), @CaseDescription ntext AS SET NOCOUNT ON INSERT INTO [dbo].[Cases] ([CaseName],[CaseDescription]) VALUES (@CaseName,@CaseDescription) SET @CaseID=SCOPE_IDENTITY() SET NOCOUNT OFF

The following example shows how to call the InsertCases stored procedure using the Microsoft .NET Framework. Notice that the @CaseID parameter is set as an output parameter by setting its Direction property to arameterDirection.Output which allows it to return the CaseID identity value after the stored procedure has been executed.

C#

Command.Parameters.Add(new System.Data.SqlClient.SqlParameter(”@CaseID”,System.Data.SqlDbType.Int));

Command.ExecuteNonQuery();

CaseID = System.Convert.ToInt32( Command.Parameters["@CaseID"].Value);

Conclusion

By using SCOPE_IDENTITY within a stored procedure, the identity value of the most recently inserted record can always be correctly obtained.