Reading SP with output parameters and result set in C#?

Question

I have created a stored procedure similar to this simplified example:

CREATE PROCEDURE dbo.sp_MyStoredProcedure
    @Var1 INT OUTPUT,
    @Var2 DECIMAL(10,2) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        @Var1 = COUNT(*),
        @Var2 = SUM(TranAmount)
    FROM
        MyTable

    SELECT * FROM MyTable
END

When I try to read the values from the output variables after I call the ExecuteReader() method of the SqlCommand object, the values are null.

string MyConnString = string.Empty;
SqlConnection MyConn = new SqlConnection(MyConnString);
SqlCommand MyCmd = new SqlCommand("sp_MyStoredProcedure", MyConn);
MyCmd.CommandType = CommandType.StoredProcedure;
MyCmd.Parameters.Add(new SqlParameter("@Var1", SqlDbType.Int));
MyCmd.Parameters.Add(new SqlParameter("@Var2", SqlDbType.Decimal);
MyCmd.Parameters[0].Direction = ParameterDirection.Output;
MyCmd.Parameters[1].Direction = ParameterDirection.Output;
SqlDataReader dr = MyCmd.ExecuteReader(CommandBehavior.CloseConnection);
int Var1 = Convert.ToInt32(MyCmd.Parameters[0].Value);
decimal Var1 = Convert.ToDecimal(MyCmd.Parameters[1].Value);

What I am doing wrong?

Answer 1

You need to read the reader till end, the output parameters are at the end of the TDS stream and the client won't see them until the result set isn't consumed.

If you must have the count and sum before you read the resultset you must ditch the OUTPUT params. Just produce an ordinary result set with the two values you're interested in folowed buy the SELECT * result set. Then read both result sets in the client using SqlDataReader.NextResult().

Update

Here is what I mean by having two results set:

CREATE PROCEDURE dbo.sp_MyStoredProcedure    
AS
BEGIN    
  SET NOCOUNT ON;    
  SELECT COUNT(*) as cnt, SUM(TranAmount) as sum_ta
  FROM MyTable
  SELECT * FROM MyTable
END

and the client:

string MyConnString = string.Empty;
SqlConnection MyConn = new SqlConnection(MyConnString);
SqlCommand MyCmd = new SqlCommand("sp_MyStoredProcedure", MyConn);
MyCmd.CommandType = CommandType.StoredProcedure;
using(SqlDataReader dr = MyCmd.ExecuteReader(CommandBehavior.CloseConnection))
{
  while(dr.Read())
  { 
     count = dr["cnt"];
     sum = dr["sum_ta"];
  }
  dr.NextResult();
  while(dr.Read())
  {
    // process MyTable row here
  }
}

Note that you do not need to do this is your C# code does not need the value of the output parameters before consuming the data reader. You can simply read the SqlDataReader till end and then check the output parameters, they will be set.

Answer 2

As Remus said, if you need it first, then you need to readdress how you query. Why not have two separate procedures? That would be the best solution in abstract.

Otherwise, you can do two SQL statements int he same batch

SELECT COUNT(*) AS VAR1, SUM(TranAmount) AS VAR2 FROM MyTable
SELECT * FROM MyTable

Then when you call ExecuteReader

SqlDataReader dr = MyCmd.ExecuteReader(CommandBehavior.CloseConnection);
while (dr.read())
{
    var1 = dr["Var1"];
    var2 = dr["Var2"];
}

dr.NextResult();

while (dr.read())
{
   // blah blah blah code
}