Retrieving 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 the id in C# and put it output in c#:

cmd.Parameters.AddWithValue("@guidid",_id);//_id is SqlGuid

cmd.Parameters.AddWithValue("@sname", "mehdi");

cmd.ExecuteNonQuery();
MessageBox.Show(_id.ToString());

but messagebox show the 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 doesn't return anything

Solution 1

First of all - if it's 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 the 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 this in your SQL Server Mgmt Studio - does it show anything??

DECLARE @insertedID UNIQUEIDENTIFIER

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

SELECT @insertedID

and in your C# - you have to read out 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());

Solution 2

Before you execute the query you need to specify the direction of the parameter, in this case output. e.g.:

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());

Comments

[Thomas Levesque]

It should be Parameters["@guidid"] (square brackets rather than parens)

[and_the_rand]

That sounds contagious. Be careful! ;-P