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

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.

Answer 4

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 5

OK, but how is the right way to check it on C# side. Is null on my side. Why?

As Viorel pointed out: the value is set on completion of the SqlCommand and after the SqlDataReader is closed". So you must exhaust the Reader by calling Read() until it returns False, and then on an outer loop you need to call NextResult(). Not until then you can retrieve the value of output parameters.

To be honest, it is not very fruitful to learn data-access basics through forum questions, because you only get bits and pieces. It's better to get a book to learn things from start to end. Or attend a class.

Here is a book that I used to learn ADO .NET once upon a time: https://www.amazon.com/Programming-Microsoft-ADO-NET-Reference-Developer/dp/073562206X/ref=sr_1_2?crid=S5RZEKR0BQS5&keywords=david+data+ado&qid=1642027013&s=books&sprefix=david+datta+ado%2Cstripbooks-intl-ship%2C132&sr=1-2. It's from 2005, so it is not really up to date with regards to the C# language. But the basics of ADO .NET has not changed, so I think it is still good for that.