Q. I called a stored procedure that returns a set of rows. Everything seems to work except that the output and return parameters are empty. Why is that?

A. You can think of a stored procedure as a function in your code. The function doesn’t return a value until it has executed all of its code. If the stored procedure returns results and you haven’t finished processing these results, the stored procedure hasn’t really finished executing. Until you’ve closed the DataReader, the return and output parameters of your Command won’t contain the values returned by your stored procedure.

Let’s say we have the stored procedure

CREATE PROCEDURE RowsAndOutput (@OutputParam int OUTPUT) AS
    SELECT @OutputParam = COUNT(*) FROM Customers
    SELECT CustomerID, CompanyName, ContactName, Phone FROM Customers

and we call it with the following code:

string strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" + 
                 "Initial Catalog=Northwind;Trusted_Connection=Yes;";
OleDbConnection cn = new OleDbConnection(strConn);
cn.Open();

string strSQL = "{CALL RowsAndOutput(?)}";
OleDbCommand cmd = new OleDbCommand(strSQL, cn);
OleDbParameter param;
param = cmd.Parameters.Add("@OutputParam", OleDbType.Integer);
param.Direction = ParameterDirection.Output;
OleDbDataReader rdrCustomers = cmd.ExecuteReader();
Console.WriteLine("After execution - " + (string) param.Value);
while (rdrCustomers.Read())
{}
Console.WriteLine("After reading rows - " + (string) param.Value);
while (rdrCustomers.NextResult())
{}
Console.WriteLine("After reading all results - " + 
                  (string) param.Value);
rdrCustomers.Close();
Console.WriteLine("After closing DataReader - " + 
                  (string) param.Value);

Even though the stored procedure sets the value of the output parameter before running the query that returns rows from the Customers table, the value of the output parameter is not available until after the DataReader is closed.