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
Using Output Parameters
The following Stored Procedure fetches the Identity value and returns it using an Output parameter.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[AddEmployeeReturnIDwithoutput]
@FirstName varchar(50),
@LastName varchar(50),
@BirthDate datetime,
@City varchar(50),
@Country varchar(50),
@id int output
AS
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.
C#
//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
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[AddEmployeeReturnID]
@FirstName varchar(50),
@LastName varchar(50),
@BirthDate datetime,
@City varchar(50),
@Country varchar(50)
AS
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.
C#
//Without Output parameter
String strConnString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
SqlConnection con = new SqlConnection(strConnString);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "AddEmployeeReturnID";
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.Connection = con;
try
{
con.Open();
object obj = cmd.ExecuteScalar();
lblMessage.Text = "Record inserted successfully. ID = " + obj.ToString() ;
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
con.Dispose();
}
VB.Net
'Without 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 = "AddEmployeeReturnID"
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.Connection = con
Try
con.Open()
Dim obj As Object = cmd.ExecuteScalar()
lblMessage.Text = "Record inserted successfully. ID = " & obj.ToString()
Catch ex As Exception
Throw ex
Finally
con.Close()
con.Dispose()
End Try
Downloads
