How to receive an integer as a return value into C# from SQL Server stored procedure?

Question

Is there a way to obtain an integer return code value back into a C# program from an SQL Server stored procedure without having to define an additional parameter in the stored procedure as an output (or input/output) parameter?

My app has to call a stored procedure that will be written elsewhere and that allegedly returns an integer value as a return code. A simple little stub stored procedure that I threw together for testing follows, as well as one version of the C# code - there have been many varied attempts.

ALTER PROCEDURE [dbo].[MyTestSP]
    @OrderNumber INT
    , @LineNumber INT
    , @ActionCode INT -- code that decides course of action to take
    , @DummyParm  INT
AS
BEGIN
    BEGIN TRY   
        DECLARE
            @sql NVARCHAR(max)
            , @ReturnCode INT = 57
            , @ActionTaken BIT = 0

        BEGIN
            SET @ReturnCode = @OrderNumber + 100;

            SET @ActionTaken = 1;
        END
    END TRY     
    BEGIN CATCH     
        SET @ReturnCode = 901;
    END CATCH

    RETURN @ReturnCode;
END

C# code:

using (SqlCommand cmd = new SqlCommand("MyTestSP1", sqlConn1))
{
   cmd.CommandType = CommandType.StoredProcedure;

   cmd.Parameters.AddWithValue("@OrderNumber", Convert.ToInt32(textBox8.Text));
   cmd.Parameters.AddWithValue("@LineNumber", 2);
   cmd.Parameters.AddWithValue("@ActionCode", 0);

   sqlConn1.Open();
   rtVal = (int) cmd.ExecuteNonQuery();
   sqlConn1.Close();
}

The above received a null value back on the stored procedure call.

You're not defining anything for the @DummyParm, and also, in your C# code, you're calling MyTestSP1 while the SQL code shows MyTestSP ..... — marc_s
– marc_s, Commented Jun 4, 2014 at 17:03

Cam Bruce · Accepted Answer · 2014-06-04 17:07:23Z

1

You want to add a parameter with your return value name and set the SqlParmenter.Direction to ParameterDirection.ReturnValue

    var parameter = new SqlParameter();
    parameter.Name = "@ReturnCode";
    parameter.Direction = ParameterDirection.ReturnValue;

    cmd.Parameter.Add(parameter);
    // execute sql..

    var value = parameter.Value;

edited Jun 4, 2014 at 17:07

answered Jun 4, 2014 at 17:02

Cam Bruce

5,68924 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

marc_s Over a year ago

And you'll also need to call cmd.Parameters.Add(parameter); for this to work!

Cam Bruce Over a year ago

Yup. This is just a snippet illustrating how to setup a return value parameter.

Collectives™ on Stack Overflow

How to receive an integer as a return value into C# from SQL Server stored procedure?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related