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) > =================================== 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
