Possible to return an out parameter with a DataReader

Question

Using ExecuteReader I am able to return a DataReader, but the out parameter is returning 0.

Using ExecuteNonQuery I am able to retrieve the out parameter (with the correct value) but the ExecuteNonQuery does not return a DataReader.

Here is the procedure to give context:

SQL Query:

CREATE PROCEDURE [dbo].[SelectDays]
    @dateStart datetime,
    @dateEnd datetime,
    @recordCount bigint out
AS
BEGIN
    select @recordCount = count(*)
    from dbo.[Days]
    where [Date]>=@dateStart and [Date]<=@dateEnd;

    select [ID],[Name]
    from dbo.[Days]
    where [Date]>=@dateStart and [Date]<=@dateEnd;
END

Is there a way I could return a DataReader as well as the out parameter, or should I create two separate procedures for each?

C# code:

     Int32 returnValue = 0;

     Parameters parameters = new Parameters();
     parameters.Add(new SqlParameter("@dateStart", dateStart != null ? (object)dateStart : DBNull.Value));
     parameters.Add(new SqlParameter("@dateEnd", dateEnd != null ? (object)dateEnd : DBNull.Value));
         SqlParameter out_recordCount = new SqlParameter("@recordCount", SqlDbType.BigInt);
         out_recordCount.Direction = ParameterDirection.InputOutput;
         out_recordCount.Value = recordCount;
         parameters.Add(out_recordCount);

     SqlParameter return_Value = new SqlParameter("@RETURN_VALUE", SqlDbType.Int);
     return_Value.Direction = ParameterDirection.ReturnValue;
     parameters.Add(return_Value);

     dataReader = this.command.ExecuteReader("dbo.SelectDays", CommandType.StoredProcedure, parameters.ToArray());

     if(out_recordCount.Value != DBNull.Value)
     recordCount = Convert.ToInt64(out_recordCount.Value);

     returnValue = Convert.ToInt32(return_Value.Value);

     return returnValue;

Can you show your C# code? Are you setting the `CommandType` to `CommandType.StoredProcedure`? How are you passing the parameters to the command? — juharr, Aug 17 '15 at 14:14
Maybe [this](http://stackoverflow.com/questions/6374193/sql-output-stored-procedures-not-working-with-executereader) will help. — juharr, Aug 17 '15 at 14:18
What exactly is `command`? I'm not familiar with an overload of `SqlCommand.ExecuteReader` that takes the command text, type, and parameters. — juharr, Aug 17 '15 at 14:23

score 16 · Answer 1 · edited Nov 17 '17 at 15:17

The value for your output parameter is in the stream from SQLServer AFTER any results sets returned (I believe this is also true of the return value). That means you won't see the value until after you read all the rows from the DataReader (or close it I believe). So an output parameter that tells you the number of rows in the result set is of little use.

However, the code fragment below demonstrates the sequence of operations you should be using:

using(SqlConnection connection = new SqlConnection("[your connection string here]"))
{
  connection.Open();

  using (SqlCommand command = connection.CreateCommand())
  {
    command.CommandType = CommandType.StoredProcedure;
    command.CommandText = "dbo.SelectDays";

    command.Parameters.AddWithValue("@dateStart", dateStart != null ? (object)dateStart : DBNull.Value);
    command.Parameters.AddWithValue("@dateEnd", dateEnd != null ? (object)dateEnd : DBNull.Value);

    SqlParameter out_recordCount = new SqlParameter("@recordCount", SqlDbType.BigInt);
    out_recordCount.Direction = ParameterDirection.InputOutput;
    out_recordCount.Value = recordCount;

    command.Parameters.Add(out_recordCount);

    SqlParameter return_Value = new SqlParameter("@RETURN_VALUE", SqlDbType.Int);
    return_Value.Direction = ParameterDirection.ReturnValue;
    command.Parameters.Add(return_Value);

    using(SqlDataReader reader = command.ExecuteReader())
    {
      while(reader.Read()) { /* do whatever with result set data here */ }
    }

    /* Output and return values are not available until here */

    if (out_recordCount.Value != DBNull.Value)
      recordCount = Convert.ToInt64(out_recordCount.Value);

    returnValue = Convert.ToInt32(return_Value.Value);

    return returnValue;
  }
}

Appreciate your time, but I need to return the reader. The above solution would work, but the `Read()` needs to be done outside of the method. I decided to separate the two methods. — Pierre Nortje, Aug 18 '15 at 07:18
This was pretty helpful. Needed to do an extra ```reader.NextResult()``` for the reader to become closed and the parameter value to be populated — Stevie, Jul 29 '22 at 12:41

score 3 · Answer 2 · answered Apr 16 '18 at 07:51

To get the values from OUTPUT parameters with SqlDataReader you can only after the reader will be closed.

So you need to add this code before you try to get the values

if(!dataReader.IsClosed)
    dataReader.Close(); 
 if(out_recordCount.Value != DBNull.Value)
     recordCount = Convert.ToInt64(out_recordCount.Value);
 returnValue = Convert.ToInt32(return_Value.Value);

Possible to return an out parameter with a DataReader

SQL Query:

C# code:

2 Answers2

Linked

Related