Getting SQL Server Output Variables in C #

Question

I have a stored procedure:

ALTER PROCEDURE [dbo].[pr_Tbl_Test_Insert] @guidid uniqueidentifier output, @sname nvarchar(50) AS -- INSERT a new row in the table. INSERT [dbo].[Tbl_Test] ( [id], [name] ) VALUES ( ISNULl(@guidid, (newid())), @sname ) 

I need id in C # and put its output in C #:

 cmd.Parameters.AddWithValue("@guidid",_id);//_id is SqlGuid cmd.Parameters.AddWithValue("@sname", "mehdi"); cmd.ExecuteNonQuery(); MessageBox.Show(_id.ToString()); 

but the message box displays a null value!

How can I return the id?

I changed it to:

 ALTER PROCEDURE [dbo].[pr_Tbl_Test_Insert] @guidid uniqueidentifier output, @sname nvarchar(50) AS DECLARE @NewID UNIQUEIDENTIFIER SET @NewID = newid(); -- INSERT a new row in the table. INSERT [dbo].[Tbl_Test]([id], [name]) VALUES(@NewID, @sname); SET @guidid = @NewID 

and c #

 SqlParameter outparam = cmd.Parameters.Add("@guidid",SqlDbType.UniqueIdentifier); outparam.Direction = ParameterDirection.Output; cmd.Parameters.AddWithValue("@sname", "mehdi"); cmd.ExecuteNonQuery(); MessageBox.Show(_id.Value.ToString()); 

but it returns nothing

Answer 1

First of all - if it is an OUTPUT parameter, you cannot use .AddWithValue in C # - you need to use:

 SqlParameter outParam = cmd.Parameters.Add("@guidid", SqlDbType.Uniqueidentifier); outParam.Direction = ParameterDirection.Output; 

and also in your T-SQL code you need to assign a new value to the output parameter!

 ALTER PROCEDURE [dbo].[pr_Tbl_Test_Insert] @guidid uniqueidentifier output, @sname nvarchar(50) AS DECLARE @NewID UNIQUEIDENTIFIER SET @NewID = newid(); -- INSERT a new row in the table. INSERT [dbo].[Tbl_Test]([id], [name]) VALUES(@NewID, @sname); SET @guidid = @NewID 

Update: if you run it in your Mgmt Studio SQL Studio - nothing is displayed

 DECLARE @insertedID UNIQUEIDENTIFIER EXEC dbo.pr_Tbl_Test_Insert @guidid = @insertedID OUTPUT, @sname = N'TestUser' -- nvarchar(50) SELECT @insertedID 

and in your C # you should read the value of the output parameter after calling ExecuteNonQuery !

 SqlParameter outparam = cmd.Parameters.Add("@guidid",SqlDbType.UniqueIdentifier); outparam.Direction = ParameterDirection.Output; cmd.Parameters.AddWithValue("@sname", "mehdi"); cmd.ExecuteNonQuery(); Guid newlyInsertedID = new Guid(cmd.Parameters["@guidid"].Value); MessageBox.Show(newlyInsertedID.ToString()); 

Answer 2

Before executing the request, you need to indicate the direction of the parameter, in this case output. eg:.

 cmd.Parameters.AddWithValue("@guidid",_id);//_id is SqlGuid cmd.Parameters.AddWithValue("@sname", "mehdi"); cmd.Parameters["@guidid"].Direction = ParameterDirection.Output cmd.ExecuteNonQuery(); MessageBox.Show(cmd.Parameters["@guidid"].Value.ToString()); 

Answer 3

You need to build SqlParameter using one of the constructors, which allows you to specify a ParameterDirection , for example this one . Alternatively, create your own parameter, and then set the direction using the Direction property.

Check this link on MSDN for more information.

Answer 4

Why are you setting the @guidid uniqueidentifier output as the output parameter? This means that it will cancel it after executing the stored procedure. If this is your intention, you need to add the statement after the insert statement to set the output parameter to the value you need. something like this: select @guidid = @generatedID. Yes, look at the marc_s code, the way you should do it.

Answer 5

I also found this very frustrating, and I could not understand this problem. Although many answers are correct, there was one simple line that I and others often overlooked, namely the command should be a stored procedure, not just sql with parameters, so I hope this helps:

  cmd.CommandType = CommandType.StoredProcedure; 

cmd.Txt should look like this:

  @"my_stored_proct " 

NOT

  @"my_stored_proct @p1, @p2, @p3 out" 

So, all together. You can divide it into several methods. and add TimeOuts etc. However, this is what I consider to be critical parts that differ from other teams without output parameters.

  using (SqlCommand cmd= new SqlCommand()) { cmd.Text= ...; cmd.CommandType = CommandType.StoredProcedure; SqlParameter outParam = cmd.Parameters.Add("@guidid", SqlDbType.Uniqueidentifier); outParam.Direction = ParameterDirection.Output; using (var connection = new SqlConnection(this.myConnectionString)) { connection.Open(); cmd.Connection = connection; try { cmd.ExecuteNonQuery(); } catch { // put your sql catches etc. here.. throw; } } var outValue = outParam.Value; //query outValue eg ToString() }