CODEWITHSUNDEEP
c#sql-serverstored-procedures
user3707903
user3707903

Reputation: 1

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

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.

Upvotes: 0

Views: 1265

Answers (1)

Cam Bruce
Cam Bruce

Reputation: 5689

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;

Upvotes: 1

Related Questions