Ferrysoft Help Desk

Click to learn more about Ferrysoft Help Desk

Ferrysoft Help Desk is a web based help desk solution. The Express Edition is free of charge. Click the screen shot to learn more about Ferrysoft Help Desk.

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 nvarchar(max) AS
INSERT INTO [dbo].[Cases] ([CaseName],[CaseDescription])
    VALUES (@CaseName,@CaseDescription)

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 ParameterDirection.Output which allows it to return the CaseID identity value after the stored procedure has been executed.

Visual Basic
Dim Connection As System.Data.SqlClient.SqlConnection
Dim Command As System.Data.SqlClient.SqlCommand
Dim CaseID As Integer
Connection = New System.Data.SqlClient.SqlConnection()
Command = New System.Data.SqlClient.SqlCommand()
Command.Connection = Connection
Connection.ConnectionString = "Server=MySvr;Database=MyDb;Integrated Security=SSPI"
Command.CommandText = "InsertCases"
Command.CommandType = System.Data.CommandType.StoredProcedure
Command.Parameters.Add("@CaseID", System.Data.SqlDbType.Int)
Command.Parameters.Add("@CaseName", System.Data.SqlDbType.NVarChar)
Command.Parameters.Add("@CaseDescription", System.Data.SqlDbType.NVarChar, -1)
Command.Parameters("@CaseID").Direction = System.Data.ParameterDirection.Output
Command.Parameters("@CaseName").Value = "New Case Name"
Command.Parameters("@CaseDescription").Value = "New Case Description"
CaseID = System.Convert.ToInt32(Command.Parameters("@CaseID").Value)
System.Data.SqlClient.SqlConnection Connection;
System.Data.SqlClient.SqlCommand Command;
int CaseID;
Connection = new System.Data.SqlClient.SqlConnection();
Command = new System.Data.SqlClient.SqlCommand();
Command.Connection = Connection;
Connection.ConnectionString = "Server=MySvr;Database=MyDb;Integrated Security=SSPI";
Command.CommandText = "InsertCases";
Command.CommandType = System.Data.CommandType.StoredProcedure;
Command.Parameters.Add("@CaseID", System.Data.SqlDbType.Int);
Command.Parameters.Add("@CaseName", System.Data.SqlDbType.NVarChar);
Command.Parameters.Add("@CaseDescription", System.Data.SqlDbType.NVarChar, -1);
Command.Parameters["@CaseID"].Direction = System.Data.ParameterDirection.Output;
Command.Parameters["@CaseName"].Value = "New Case Name";
Command.Parameters["@CaseDescription"].Value = "New Case Description";
CaseID = System.Convert.ToInt32(Command.Parameters["@CaseID"].Value);


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

About the author

Mike Green is the founder of Ferrysoft, a software development company specialising in Help Desk software technology.