I've had the same problem getting return values out of stored procedures when using a Data Reader. I ended up using the T-SQL RAISEERROR statement with a severity of at least 11. As I remember, I also needed to use sp_addmessage to add the error message to the db first. With these things in place the RAISEERROR will trigger an .NET SqlException immediately. You can then be sure, that if you do not hit your catch clause when executing ExecuteReader, no error occured.
On Tue, 17 Feb 2004 13:03:10 -0500, Kevin Hegg <[EMAIL PROTECTED]> wrote: >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 =================================== 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
