Output last inserted row id in TSQL in a stored procedure

It is quite easy to get the last inserted row id in TSQL.

Just create a stored procedure as below.

CREATE PROCEDURE InsertRow
( @parameter1 int, @parameter2 varchar(255), @NewId int OUTPUT )
AS

BEGIN
INSERT INTO table(parameter1, parameter2) VALUES(@parameter1, @parameter2)
SELECT @NewId=SCOPE_IDENTITY()
END
RETURN

… then; use it in a function like this:

public int InsertRowGetNewId(int intParam, string stringParam)
{
static string connectionString =      WebConfigurationManager.ConnectionStrings["connection"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand("InsertRow", conn);

cmd.CommandType = CommandType.StoredProcedure;
SqlParameter oParam =cmd.Parameters.AddWithValue("@parameter1", intParam);

oParam =cmd.Parameters.AddWithValue("@parameter2", stringParam);

oParam = cmd.Parameters.AddWithValue("@NewId", 0);

//give a dummy value for the "@NewId" parameter

oParam.Direction = ParameterDirection.Output;

//set the direction of the parameter as output

cmd.ExecuteNonQuery();
conn.Close();
int newId = (int) cmd.Parameters["@NewId"].Value;

return newId;

}
}
Advertisements

2 Comments

  1. Alex said,

    October 3, 2008 at 3:43 pm

    Thanks for the information. Added you to bookmark))
    Your new reader.

  2. September 23, 2010 at 9:36 pm

    Wonderful tip for tracking rowid.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: