SQL Stored Procedure - Return Value

Question

Hello,
my StoredProcedure looks like this.

USE [MeineDatenbank]
GO
/****** Object:  StoredProcedure [dbo].[GetOrders]    Script Date: 12.01.2022 14:33:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[GetOrders]
  @ProductName varchar(50)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

  SELECT        [Order].Name AS Auftrag, [Order].Quantity AS Menge
FROM            [Order] INNER JOIN
                         OrderProduct ON [Order].OrderId = OrderProduct.OrderId
WHERE             [OrderProduct].Product = @ProductName

return 4712
 --Select @TotalCount = COUNT(ID) from tblEmployee
END

I would now like to query the ReturnValue in C#.
How do I do that?

  using (SqlCommand cmd = new SqlCommand("dbo.GetOrders", con, transaction))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        //SqlParameter retVal = cmd.Parameters.Add("RetVal", SqlDbType.Int);

                        SqlParameter retVal = cmd.CreateParameter();
                        retVal.Direction = ParameterDirection.ReturnValue;
                        cmd.Parameters.Add(retVal);


                        SqlParameter IdIn = cmd.Parameters.Add("@ProductName", SqlDbType.VarChar, 50);
                        IdIn.Direction = ParameterDirection.Input;
                        IdIn.Value = "Smartphone";    

                        cmd.Prepare();

                        //int row = cmd.ExecuteNonQuery();

                        using (SqlDataReader reader = cmd.ExecuteReader())
                        {

                            var result = retVal.Value;    
                            //int rtn = (int)cmd.Parameters["@return_value"].Value;
                            int rtn = (int)cmd.Parameters["@Return Value"].Value;

Answer 1

Here is a mock-up example where the SQL is omitted, the values being returned are set from whatever query to run.

CREATE PROCEDURE [dbo].[uspDummy]     
    @FirstName nvarchar(50) OUT,   
    @LastName nvarchar(200)OUT,   
    @Identity int OUT   
AS   
BEGIN   
  
    SET NOCOUNT ON;   
  
	/*  
	  one or more queries go here  
	*/  
  
	SET @Identity = 100  
	SET @FirstName = N'Karen'  
	SET @LastName = N'Payne'  
END   

After ExecuteNonQuery in read the parameter values

public void CodeSample()  
{  
      
    string connectionString = "Data Source=.\\SQLEXPRESS;Initial Catalog=CustomerDatabase;" +  
                              "Integrated Security=True";  
  
    using (var cn = new SqlConnection(connectionString))  
    {  
        using (var cmd = new SqlCommand() {Connection = cn})  
        {  
            cmd.CommandType = CommandType.StoredProcedure;  
            cmd.CommandText = "[uspDummy]";  
            cmd.Parameters.Add(new SqlParameter()  
            {  
                Direction = ParameterDirection.Output,   
                ParameterName = "@Identity",   
                SqlDbType = SqlDbType.Int  
            });  
  
            cmd.Parameters.Add(new SqlParameter()  
            {  
                Direction = ParameterDirection.Output,   
                ParameterName = "@FirstName",   
                SqlDbType = SqlDbType.NVarChar,   
                Size = 255  
            });  
  
            cmd.Parameters.Add(new SqlParameter()  
            {  
                Direction = ParameterDirection.Output,   
                ParameterName = "@LastName",   
                SqlDbType = SqlDbType.NVarChar,   
                Size = 255  
            });  
  
            cn.Open();  
            cmd.ExecuteNonQuery();  
  
            Console.WriteLine(cmd.Parameters["@Identity"].Value);  
            Console.WriteLine(cmd.Parameters["@FirstName"].Value);  
            Console.WriteLine(cmd.Parameters["@LastName"].Value);  
        }  
  
    }  
}  

Answer 2

According to documentation, "the value is set on completion of the SqlCommand and after the SqlDataReader is closed".

Therefore try reading from retVal.Value (or from cmd.Parameters["@return Value"].Value if you assign a name to retVal parameter) after the using block:

using( SqlDataReader reader = cmd.ExecuteReader( ))  
{  
    while( reader.Read( ) )  
    {  
        // . . .  
    }  
}  
  
var result = retVal.Value;   

Answer 3

a sql query can return multiple result sets, and out of band messages, and is stored procedure call, the return value (which is actually a type of output parameter). the return value is only available after all result sets for the stored procedure are returned.

the return value of a stored procedure is actually one of the parameters which has the type Return. there are also input and output parameters. just like the return parameter, output parameters are only valid after all result sets are read.

if using a data reader, you need to read all result sets first. calling

reader.Close()

forces a read of all the result sets, so the return value is available. its the same as:

    while(reader.read()) {}
    while(reader.NextResult()) 
    {
       while(reader.read()) {}
    }

Answer 4

simple net 6 console app that reads multiple result sets, and sp return value without using .Close().

using System.Data;
using System.Data.SqlClient;

using (var connection = new SqlConnection("Server=.;Database=tempdb;Trusted_Connection=True;"))
{
    connection.Open();
    var command = new SqlCommand("", connection);

    // execute query that returns two result sets each of one row
    command.CommandText = @"
        set nocount on;
        select 1 as v1;
        select 2 as v2;
    ";
    using (var reader = command.ExecuteReader())
    {
        //read v1 (only one row)
        reader.Read();
        var v1 = reader.GetInt32(0);
        Console.WriteLine(v1);

        // next result set;
        reader.NextResult();

        //read v2
        reader.Read();
        var v2 = reader.GetInt32(0);
        Console.WriteLine(v2);
    }

    // create temp proc that returns one row and a value
    command.CommandText = @"create procedure #echo 
        @v3 as int 
    as 
        select @v3 as v3
        return @v3+1";
    command.ExecuteNonQuery();

    // call stored proc that returns 1 row and a return value
    command.CommandText = "#echo";
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.AddWithValue("@v3", 3);
    var returnParameter = command.Parameters.Add("@ReturnVal", SqlDbType.Int);
    returnParameter.Direction = ParameterDirection.ReturnValue;

    using (var reader = command.ExecuteReader())
    { 
        //read sp resultset
        reader.Read();
        var v3 = reader.GetInt32(0);
        Console.WriteLine(v3);

        //next result to return value result
        reader.NextResult();

        //get return value
        var v4 = returnParameter.Value;
        Console.WriteLine(v4);
    }
}

Answer 5

You should avoid this. Stored procs do not return values. The return of a stored proc is "Success" or "Failure". Some processes depend on this functionality. It is not intended to return actual "data" to the caller.

Unless documented otherwise, all system stored procedures return a value of 0. This indicates success and a nonzero value indicates failure.