There is no better approach. This is the way SQL Server stored procedures have always worked.
FYI. Your use of @@ERROR and @@ROWCOUNT is a bit risky. Global variables are cleared and reset on each statement executed. If you are going to do any processing on global variables that is more than one line of Transact-SQL you should assign them to local variables as shown below. CREATE PROCEDURE spDo AS DECLARE @l_error int DECLARE @l_count int SET NOCOUNT ON SELECT name FROM Author SELECT @l_error = @@ERROR, @l_count = @@ROWCOUNT IF (@l_error <> 0) RETURN @l_error IF (@l_count < 2) RETURN (-1) -----Original Message----- From: Moderated discussion of advanced .NET topics. [mailto:[EMAIL PROTECTED] On Behalf Of Alex Ivanoff Sent: Tuesday, February 17, 2004 12: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 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
