In this part I am explaining the most frequently asked question how to get the ID of the newly inserted record.
SQL Server Concepts
To get the ID of the newly inserted record there are three methods available. Now the question arises what to use hence below is the description of each of them
@@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.
Insert Stored Procedures returning ID
This is a very frequent question on forums, how to get ID of the newly inserted record. So here it is. It can be simply achieved with the SCOPE_IDENTITY() inSQL Server which stores the ID of the newly inserted record. Now to get this ID to the front end, you will need to do use one of the following 2 methods
1. Using Output Parameters
2. Using Scalar Value
Using Output Parameters
Below is the stored procedure to insert a record and return the ID. As you will notice I am using a output parameter called @ID in which I store the value of SCOPE_IDENTITY() and return it.
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
Now in order to call the above stored procedure using ADO.Net refer below. You will notice that adding an additional parameter @id and also setting its Direction as Output. Later after the ExecuteNonQuery() command is executed it has the value of the ID of the newly inserted record which I am displaying using a label.
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
There is one more a very straight forward way to get the ID of the newly inserted record by making use of ExecuteScalar() which can return one Scalar value without using output parameters that is one row and one column (a single cell). If you have to return something like that ExecuteScalar() is a good option Since many times output parameters cannot be used for example you cannot return image data type using output parameters.
Here I’ll be using ExecuteScalar() to get the ID of newly inserted record for that you just need to use SELECT statement in your stored procedure as below.
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
Now to get the ID of the newly inserted record using ADO.Net in front end is quite simple. Just get the value returned by ExecuteScalar() in an object and type cast the object to the desired data type as I have done to string
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
This completes the part 3 of the Stored Procedure Article. You can download the source in VB.Net and C# along with the scripts using the link below.
