Unfortunately, no. Output parameters (which includes the result given on RETURN) are always sent by SQL Server _after_ all resultsets (these may be more than once) have been sent and received by the consumer. It is a deficiency - or call it a quirk, if you want - of the inner workings of SQL Server and its wire-level protocol, not of any particular client library or data access wrapper.
AFAIK there is no universal way to deal with this. What's more, you have to be careful of the number of resultsets your SP returns - these might be 0, 1 or more, depending on when your RETURN statement executed. You have to walk forward through all rows in these resultsets before you get access to any output params. -----Original Message----- From: Moderated discussion of advanced .NET topics. [mailto:[EMAIL PROTECTED] On Behalf Of Alex Ivanoff Sent: Tuesday, February 17, 2004 7:16 PM To: [EMAIL PROTECTED] Subject: [ADVANCED-DOTNET] Stor proc return values and DataReader Lets say I have a stor proc like this: CREATE PROCEDURE spDo AS SET NOCOUNT ON SELECT name FROM Author IF (0<>@@ERROR) RETURN @@ERROR IF (2 > @@ROWCOUNT) RETURN (-1) GO In the code I have something like this: SqlCommand cmd = new SqlCommand(); ... SqlDataReader reader = cmd.ExecuteReader(); This is where the problem begins. In order to get return value from stor proc I have to close the reader. Basically, I read all the data, close reader, find out that errors occurred and throw the data away. It does not seem to be right. Is there a better approach to this? Thank you, Alex Ivanoff =================================== This list is hosted by DevelopMentorR http://www.develop.com Some .NET courses you may be interested in: NEW! Guerrilla ASP.NET, 17 May 2004, in Los Angeles http://www.develop.com/courses/gaspdotnetls View archives and manage your subscription(s) at http://discuss.develop.com =================================== This list is hosted by DevelopMentor� http://www.develop.com Some .NET courses you may be interested in: NEW! Guerrilla ASP.NET, 17 May 2004, in Los Angeles http://www.develop.com/courses/gaspdotnetls View archives and manage your subscription(s) at http://discuss.develop.com
