How to create and use an "out" parameter with a SQL Server database

Question

I am working on a C# desktop application. I want to use a stored procedure (insert query) to add a new record to a data table. I need to return the primary key which is an identity insert (bigint) to avoid another round trip to the database. I have searched the MS Help(less) files for a couple of hours and found nothing on how to set the out parameter of the stored procedure or how to obtain the return value.

Answer 1

What you want is an OUTPUT Parameter. I'm not a C# developer, so I've omitted the C# side of things and linked an answer:

--Sample Table
CREATE TABLE dbo.YourTable (ID bigint IDENTITY,
                            SomeColumn varchar(20));

GO
--Sample SP
CREATE PROC dbo.InsertYourTable @SomeColumn varchar(20), @ID bigint OUTPUT AS
BEGIN

    DECLARE @IDt table (ID bigint);

    INSERT INTO dbo.YourTable(SomeColumn)
    OUTPUT inserted.ID INTO @IDt
    VALUES(@SomeColumn);

    SET @ID = (SELECT ID FROM @IDt);
END;

GO
--Sample EXEC of proc and PRINT of OUTPUT value of ID
DECLARE @SomeColumn varchar(20) = 'abc', @ID bigint;

EXEC dbo.InsertYourTable @SomeColumn = @SomeColumn,
                         @ID = @ID OUTPUT;

PRINT @ID;

Using this answer I think the c# would be:

cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@SomeColumn",SqlDbType.VarChar,20);
cmd.Parameters["@SomeColumn"].Value = MyColumn;
cmd.Parameters.Add("@ID",SqlDbType.int).Direction = ParameterDirection.Output;

Answer 2

I get the impression you're trying to go from three round trips, like this:

INSERT => SELECT to get key => OTHER INSERT using previous key

to two

INSERT returns the key => OTHER INSERT using previous key

We can do better.

One way to resolve this is sending more than one command in the sql string from C#. So you end up with something like this, which achieves the goal of avoiding extra round-trips to the database:

string sql = @"
   DECLARE @ID int; 
   EXEC InsertProcedure; 
   SELECT @ID = scope_identity(); 
   INSERT INTO OtherTable VALUES (@ID);";

using (var cn = new SqlConnection("connection string"))
using (var cmd = new SqlCommand(sql, cn))
{
    cn.Open();
    cmd.ExecuteNonQuery();
}

Alternatively, maybe you just want the key for use in your application, so you can continue to work with the record and have fast lookups via that new clustered index value. In that case, you can still do it this way:

string sql = @"
   DECLARE @ID int; 
   EXEC InsertProcedure; 
   SELECT scope_identity();";

using (var cn = new SqlConnection("connection string"))
using (var cmd = new SqlCommand(sql, cn))
{
    cn.Open();
    return (int)cmd.ExecuteScalar();
}

Just be careful whether the stored procedure has any unresolved select queries of it's own, in which case you'll need to use ExecuteReader(), along with it's NextResult() method to advance between SELECT query results.

Of course, there's likely more to it than that for your real code, where you need parameters for the new data to insert, but this provides the general concept.

Answer 3

Using an OUTPUT clause on the INSERT is great, and essential if you needed to capture the identity values in a multi-row batch. For a SQL beginner however the code is a little more complex, and in relation to this particular question it could be confused with the OUTPUT parameter of the stored proc, which is a completely unrelated concept.

For a single row insert however, the SCOPE_IDENTITY function works great and is simpler to use.

Here's a version of Larnu's stored proc using SCOPE_IDENTITY instead:

CREATE PROC dbo.InsertYourTable @SomeColumn varchar(20), @ID bigint OUTPUT AS
BEGIN

INSERT INTO dbo.YourTable(SomeColumn)
VALUES(@SomeColumn);

SET @ID = SCOPE_IDENTITY();
END;

Answer 4

with the help of you guys that answered, I finally figured it out. I modified the stored procedure as follows:

---

//Stored Procedure

```` -- Add the parameters for the stored procedure here
````@Vendorcode nvarchar(17) = ''
````, @Name nvarchar(60) = ''
````...
````, @ID bigint output  --<<<<<<<<Added this line
````AS
````BEGIN
````    -- SET NOCOUNT ON added to prevent extra result sets from
````    -- interfering with SELECT statements.
````    SET NOCOUNT ON;
````    -- Insert statements for procedure here
````INSERT INTO APVendorShipTo
````        (Vendorcode, [Name], ...)
````VALUES  (@Vendorcode, @Name,...)

````Select @ID = SCOPE_IDENTITY(); -- <<<<<<<< Added this line
````END
*//end Stored Procedure*
----------


----------

*//C# Code in Desktop App*

````try
````{
````string connString = AdoHelper.ConnectionString;
````var myConnection = new SqlConnection(connString);
````var myCommand = new SqlCommand();
````using (myConnection)
````{
```` myConnection.Open();
```` myCommand.Connection = myConnection;
```` myCommand.CommandType = CommandType.StoredProcedure;
```` myCommand.CommandTimeout = 540;
```` myCommand.CommandText = "APVendorShipToAddNew";     
```` myCommand.Parameters.AddWithValue(parameterName: "@Vendorcode", value:   
this.txtAcctNo.Text);
```` myCommand.Parameters.AddWithValue(parameterName: "@Name", value: this.txtShipName.Text);
-- left out the rest of the parameters
--//Added the line below to retrieve the Identity Insert value from the query
````myCommand.Parameters.Add("@ID", SqlDbType.BigInt).Direction = ParameterDirection.Output;

````myCommand.ExecuteNonQuery();

--//Added next line to convert the returned value to a usable value in my program.
````liID = Convert.ToInt64(myCommand.Parameters["@ID"].Value);

````MessageBox.Show(Convert.ToString(liID)); 
--//Showed correct value Now available to  my code

````return true;
````}