Return Identity value from Stored Procedure in SQL Server

In this article I will explain how to return Identity value from Stored Procedure in SQL Server.

The Identity value (ID value) of the newly (last) inserted record can be determined using @@IDENTITY, SCOPE_IDENTITY() and IDENT_CURRENT functions.

This article is applicable to SQL Server 2000, 2005, 2008, 2008R2, 2012 and 2014 versions.

The @@IDENTITY, SCOPE_IDENTITY() and IDENT_CURRENT functions in SQL Server

The @@IDENTITY, SCOPE_IDENTITY() and IDENT_CURRENT functions return the Identity value (ID value) of the newly (last) inserted record but still there exists a difference.

@@IDENTITY

It returns the ID newly inserted record for any table for the current connection or session, but not the current scope. Means that it will return newly inserted even if it is inserted by a trigger or user defined function. Hence use if only when you do not have triggers or functions that run automatically.

SCOPE_IDENTITY()

It returns the ID of newly inserted for the table in the current scope and current connection or session. Means it will return the newly inserted ID of the record that is done by you using a stored procedure or query and not by automatic process like trigger. Hence many times to be safer one should use SCOPE_IDENTITY()

IDENT_CURRENT(’TableName’)

It returns the ID of the newly inserted record for the table specified. Here there is no constraint of scope and session It will give you the ID of the latest record for that table.

Return Identity value from Stored Procedure in SQL Server

To return Identity value from a Stored Procedure there are two methods.

1. Using Output Parameters

2. Using Scalar Value

Note: To learn more about syntax of Stored Procedures refer my article Using Stored Procedures in SQL Server Database.

Using Output Parameters

The following Stored Procedure fetches the Identity value and returns it using an Output parameter.

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE PROCEDURE [dbo].[AddEmployeeReturnIDwithoutput]

@FirstName varchar(50),

@LastName varchar(50),

@BirthDate datetime,

@City varchar(50),

@Country varchar(50),

@id int output

BEGIN

SET NOCOUNT ON;

INSERT INTO Employees (FirstName, LastName, BirthDate, City, Country)

VALUES (@FirstName, @LastName, @BirthDate, @City, @Country)

SET @id=SCOPE_IDENTITY()

RETURN @id

END

In code behind, the Stored Procedure is supplied Input as well as the Output parameters and the value of Output parameter is fetched after the ExecuteNonQuery command is executed.

//Using Output parameter

String strConnString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;

SqlConnection con = new SqlConnection(strConnString);

SqlCommand cmd = new SqlCommand();

cmd.CommandType = CommandType.StoredProcedure;

cmd.CommandText = "AddEmployeeReturnIDwithoutput";

cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = txtFirstName.Text.Trim();

cmd.Parameters.Add("@LastName", SqlDbType.VarChar).Value = txtLastName.Text.Trim();

cmd.Parameters.Add("@BirthDate", SqlDbType.DateTime).Value = txtBirthDate.Text.Trim();

cmd.Parameters.Add("@City", SqlDbType.VarChar).Value = txtCity.Text.Trim();

cmd.Parameters.Add("@Country", SqlDbType.VarChar).Value = txtCountry.Text.Trim();

cmd.Parameters.Add("@id", SqlDbType.Int).Direction = ParameterDirection.Output;

cmd.Connection = con;

try

{

con.Open();

cmd.ExecuteNonQuery() ;

string id = cmd.Parameters["@id"].Value.ToString() ;

lblMessage.Text = "Record inserted successfully. ID = " + id;

}

catch (Exception ex)

{

throw ex;

}

finally

{

con.Close();

con.Dispose();

}

VB.Net

'Using Output parameter

Dim strConnString As String = ConfigurationManager.ConnectionStrings("conString").ConnectionString

Dim con As New SqlConnection(strConnString)

Dim cmd As New SqlCommand()

cmd.CommandType = CommandType.StoredProcedure

cmd.CommandText = "AddEmployeeReturnIDwithoutput"

cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = txtFirstName.Text.Trim()

cmd.Parameters.Add("@LastName", SqlDbType.VarChar).Value = txtLastName.Text.Trim()

cmd.Parameters.Add("@BirthDate", SqlDbType.DateTime).Value = txtBirthDate.Text.Trim()

cmd.Parameters.Add("@City", SqlDbType.VarChar).Value = txtCity.Text.Trim()

cmd.Parameters.Add("@Country", SqlDbType.VarChar).Value = txtCountry.Text.Trim()

cmd.Parameters.Add("@id", SqlDbType.Int).Direction = ParameterDirection.Output

cmd.Connection = con

Try

con.Open()

cmd.ExecuteNonQuery()

Dim id As String = cmd.Parameters("@id").Value.ToString()

lblMessage.Text = "Record inserted successfully. ID = " & id

Catch ex As Exception

Throw ex

Finally

con.Close()

con.Dispose()

End Try

Using Scalar Value

Another easy way is to return value of Identity as Scalar value i.e. a Single cell. Stored Procedures are capable of returning Scalar values of any data type.

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE PROCEDURE [dbo].[AddEmployeeReturnID]

@FirstName varchar(50),

@LastName varchar(50),

@BirthDate datetime,

@City varchar(50),

@Country varchar(50)

BEGIN

SET NOCOUNT ON;

INSERT INTO Employees (FirstName, LastName, BirthDate, City, Country)

VALUES (@FirstName, @LastName, @BirthDate, @City, @Country)

SELECT SCOPE_IDENTITY()

END

In code behind, the Identity value returned as Scalar value returned from the Stored Procedure can be fetched by making use of ADO.Net ExecuteScalar function.

//Without Output parameter