SqlCommand and sp that returns both an output variable and a result set.

  • Thread starter Thread starter JP
  • Start date Start date
J

JP

Is it possible to return both an output variable and a
result set using a SqlCommand object?

I have a stored procedure that returns both an output
variable and a resultset.
If I comment out the select statement in the sp so it just
has the output variable all works well.
When I leave the select statement intact I get this error
when using myCommand.ExecuteReader():
System.NullReferenceException: Object reference not set to
an instance of an object.
RCount = (int)parameterRCount.Value

The statment is as follows:

SqlParameter parameterRCount = new SqlParameter
("@RCount",SqlDbType.Int,10);
parameterRCount.Direction = ParameterDirection.Output;
myCommand.Parameters.Add(parameterRCount);

// Open the database connection and execute the command
myConnection.Open();
SqlDataReader dr = myCommand.ExecuteReader
(CommandBehavior.CloseConnection);
RCount = (int)parameterRCount.Value;

// Return the datareader
return dr;
 
Yes, sure it's possible to return a rowset and one or more output
parameters. There are a few tricks though. See my article on handling
parameters. http://www.betav.com/msdn_magazine.htm

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Back
Top