Bug retrieving output params and a recordset from a stored proc?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a stored procedure which returns output values and selects back a
recordset. I can't seem to get both back using ADO.NET. If I use
command.ExecuteNonQuery, I can retrieve the ouptut parameter values but no
recordset, and with ExecuteReader I can get the recordset, but the parameter
values are not updated to the values output by the stored proc. SQL Trace is
identical for both, so I'm sure the values are there. Is this an ADO.NET
bug?
 
Once you get the rowset, you'll need to move to the last row and/or close
the DataReader before the OUTPUT parameters are available.

See my article on handling "Gazintas and Gazoutas" (input and output
parameters) (www.betav.com/articles.htm)
hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
If you have a recordset coming back, you have to iterate through the results
to get the Output params. Bill Vaughn wrote a great article on this
http://www.betav.com/msdn_magazine.htm Retrieving the Gozoutas.
Here's the quote that explains it:"
If you want to capture the integer passed back by the RETURN statement in
TSQL or your stored procedure's OUTPUT parameters, you're going to have to
take another few minutes to setup a Command object to capture these values
from the resultset. Again, and I can't emphasis this enough, these values
are not made available until you've processed all rowsets in each resultset
(if there are any). Yes, a stored procedure can return several resultsets,
but the RETURN value and OUTPUT parameters are not populated until all
queries have been completed and all rowsets transmitted back to the client.
"
 
Back
Top