I believe you missed the real issue (really described only in the very first message in the thread [which hasn't been repeated], and replicated in the last message at the bottom). It's not that anyone is having trouble "figur[ing] out how to use" SP return values -- the problem is that you can't get the value of any OUTPUT parameters, or the SP return code, until after the network has transmitted (and the client machine has swallowed) all the data describing all result sets.
Using RAISERROR apparently interrupts the "here are the result sets" part of the data stream sent to the client -- at least, that's what people are thinking. The example at the very bottom shows the annoyance of not being able to see the SP return code until the entire "select name from author" result set has been returned. I haven't encountered in my work any SP which generated a result set and then figured out it wanted to return a failure indication (and the app didn't want to see the result set due to whatever the failure was), but if that's what you need to do.... At 08:57 AM 2/20/2004, Kevin Hegg wrote >Running sp_addmessage is optional, but useful if you want developers to use >a standard set of messages. > >The choice of using RAISERROR vs. stored procedure return values is >identical to the choice of exceptions vs. return values in object-oriented >languages. Each has its place. You want to be careful about overusing >RAISERROR just because you can't figure out how to use stored procedure >return values. RAISERROR is not appropriate everywhere. > >-----Original Message----- >From: Moderated discussion of advanced .NET topics. >[mailto:[EMAIL PROTECTED] On Behalf Of Morten Nicolaj >Pedersen >Sent: Friday, February 20, 2004 5:02 AM >To: [EMAIL PROTECTED] >Subject: Re: [ADVANCED-DOTNET] Stor proc return values and DataReader > >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) >> J. Merrill / Analytical Software Corp =================================== 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
