Stored proc Output parameter + Select not passing back output parameter

Question

Hi

I have a stored procedure that returns two recordsets which i call using GetReader. I iterate the first, call IDataReader.NextResult(), then iterate the second.

I assign values to output parameters in the sp, but when i check the values after finishing with my reader, my output parameters are null. Looks like a bug. I don't want to use a select since i don't like fudges. Some snippets...

...

sp.Command.AddParameter("@SelectedTabID", selectedTabID, DbType.Int32);
sp.Command.AddParameter("@CurrentTabID", 0, DbType.Int32, ParameterDirection.Output);
sp.Command.AddParameter("@TypeID", 0, DbType.Int32, ParameterDirection.Output);

(note doing it this way or using AddOutputParameter() yields same results)

...

using(IDataReader reader = sp.GetReader())
{
  while (reader.Read()) {...}
  if (reader.NextResult()) {while (reader.Read()) {...}}

}

...

int one = (int)sp.OutputValues[0]; //null expected an int
int two = (int)sp.OutputValues[1]; //null expected an int

Looking forward to some gems of wisdom :)

Answer 1

This is subsonic failing, works fine as a bog standard SQLConnection/Command

Answer 2

Slightly confused here...are you expecting output of scalar values or some kind of recordset? Get Reader would only really make sense if you have e.g. some cursor coming back as out parameter...

You may want to call "ExecuteNonQuery()" on your command and ensure that you set the CommandType correctly (CommandType.StoredProcedure)

Answer 3

The easiest way to get the output parameter is to use an instantiated variable. What I mean is keep a reference to your parameters, then once you execute your sproc, then you can inspect them like this:

SqlCommand cmd = new SqlCommand();
cmd.Connection = ConfigurationManager.ConnectionStrings["MyCon"].ToString();
cmd.CommandText = "MySproc";

SqlParameter parmOut = new SqlParameter("@StuffIWant", SqlDbType.Int);
parmOut.Direction=ParameterDirection.Output; 

cmd.Parameters.Add(parmOut);
cmd.ExecuteNonQuery();

string theThingIWant = parmOut.Value.ToString();

This may not be using a datareader, but you may be able to use this to find the right answer. Good Luck.

Answer 4

The answer is that you have to close the reader before you can access the output parameters.

From: This Page

• Make sure you retrieve the recordset first as an SqlDataReader via cmd.ExecuteReader() Process through all recordsets and then...
• Make sure you CLOSE the recordset, or SqlDataReader via reader.Close() before trying to retrieve the output parameters