Be carefull here about indicating "trigger an .NET SqlException immediately". This will ONLY be true if you have not yet issued a SELECT statement containing a result set prior to your raiserror call. Otherwise, as indicated before during prior posts, you will only get the exception by calling NextResult() while using a reader. Also, sp_addmessage is needed if you what to use message ids in the raiseerror call instead of a string message.
Chad On Fri, 20 Feb 2004 05:01:54 -0500, Morten Nicolaj Pedersen <[EMAIL PROTECTED]> wrote: >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 =================================== 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
