Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #328 > unrolled thread

SS 2008: Rethrowing User-Defined Error Codes *FULLY*

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  SS 2008: Rethrowing User-Defined Error Codes *FULLY* Gene Wirchenko <genew@ocis.net> - 2011-05-12 19:35 -0700
    Re: SS 2008: Rethrowing User-Defined Error Codes *FULLY* Erland Sommarskog <esquel@sommarskog.se> - 2011-05-13 07:43 +0000
      Re: SS 2008: Rethrowing User-Defined Error Codes *FULLY* Gene Wirchenko <genew@ocis.net> - 2011-05-13 11:22 -0700
        Re: SS 2008: Rethrowing User-Defined Error Codes *FULLY* Erland Sommarskog <esquel@sommarskog.se> - 2011-05-13 23:27 +0200

#328 — SS 2008: Rethrowing User-Defined Error Codes *FULLY*

Dear SQLers:

     I wish to define some user-defined errors.  For example:

execute sp_addmessage
 @msgnum=50001,@severity=16,
 @msgtext=N'String %s has too many lines.',@replace=N'replace'

They might have different parameter types.  For example, another might
have a money amount.

     In my stored procedures, I could have a number of raiserror()s
using them.  How do I handle this in try-catch so that the error
number returned to the caller is not 50000 but whatever I define.  I
might have a try block of:

          begin try
          ...
          raiserror(50001,16,1,N'ACUK')
          ...
          raiserror(50002,16,1,@TranBalance)
          ...
          end try

If I wish to rethrow the error and return all particulars to the
caller, is there any way to do it?  If so, easily?  I see four
possibilities, none of which do quite what I want.  Is there something
that I am overlooking?

1) raiserror() with an error message

I could do:

   begin catch
   ...
   declare
    @ErrMsg varchar(max),
    @ErrSeverity int,
    @ErrState int
   select
    @ErrMsg=error_message(),
    @ErrSeverity=error_severity(),
    @ErrState=error_state()
   raiserror(@ErrMsg,@ErrSeverity,@ErrState)
   end catch

but this will return 50000 as the error number.  I want to return the
user-defined error number.

2) raiserror() with an error number

   begin catch
   ...
   declare
    @ErrNr int,
    @ErrSeverity int,
    @ErrState int
   select
    @ErrNr=error_number(),
    @ErrSeverity=error_severity(),
    @ErrState=error_state()
   raiserror(@ErrNr,@ErrSeverity,@ErrState)
   end catch

but then I lose the substitution into the user-defined error message. 
3) Somehow keep track of the raiserror() user-defined parameters and
have as many cases as necessary in the catch to handle then.

     While this will do it, this is extremely verbose and prone to
error.  It amounts to duplicating each raiserror() user-defined error
number call, once (or more) in the try and once in the catch.

4) Abandon use of try-catch

     This works, but if I need to do some processing in the stored
procedure (not in the caller) after the error -- such as closing and
deallocating a cursor -- I am out of luck.


     Is there something that I am overlooking?

Sincerely,

Gene Wirchenko

[toc] | [next] | [standalone]

#329

Gene Wirchenko (genew@ocis.net) writes:
>      Is there something that I am overlooking?
 
Not really, I think you have summrised the alternatives quite well.

What I can add is:

1) I don't like user-defined messages as they work today in SQL Server for
   two reasons:
   a) They are defined on server-level.
   b) Numbers are not very mnemonic.

2) Look at http://www.sommarskog.se/error_handling_2005.html which is 
   a variation of your first alternative, but the re-raised message is
   formatted to make it easy to parse.

3) The next version of SQL Server will have command for re-raising errors
   exactly as they were given.



-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[toc] | [prev] | [next] | [standalone]

#330

On Fri, 13 May 2011 07:43:05 +0000 (UTC), Erland Sommarskog
<esquel@sommarskog.se> wrote:

>Gene Wirchenko (genew@ocis.net) writes:
>>      Is there something that I am overlooking?
> 
>Not really, I think you have summrised the alternatives quite well.

     Erland!  You are not doing your part!  You were supposed to tell
what I was missing.  <SIGH>  T-SQL has a lot of warts.  I fight with
the language a lot, because it violates the Law of Least Astonishment
so much.  (My most recent one is that raiserror()'s string error
message can not be a string expression.)

>What I can add is:
>
>1) I don't like user-defined messages as they work today in SQL Server for
>   two reasons:
>   a) They are defined on server-level.
>   b) Numbers are not very mnemonic.

     I am experimenting with something that could be returned to the
UI level.

     I wish that there was something like #include and #define so I
could define mnemonics for the errors I wish to define.  I have
looked, but I did not find anything.

>2) Look at http://www.sommarskog.se/error_handling_2005.html which is 
>   a variation of your first alternative, but the re-raised message is
>   formatted to make it easy to parse.

     I might go that way.  Your page is a good read.

>3) The next version of SQL Server will have command for re-raising errors
>   exactly as they were given.

     That is good in general, but I have a gotcha.  <sigh>  I will not
be able to use it for the company that the main app is for.  They will
stick with Windows XP for quite some time.  SS 2011 will not run on
XP.

Sincerely,

Gene Wirchenko

[toc] | [prev] | [next] | [standalone]

#331

Gene Wirchenko (genew@ocis.net) writes:
>      Erland!  You are not doing your part!  You were supposed to tell
> what I was missing.  

As a matter of fact, I did! I told you were missing the most recent 
version of SQL Server, if only in beta. :-)

> I fight with the language a lot, because it violates the Law of Least
> Astonishment so much.  

Sometimes I think that maximising the astonishment has been a prime
design goal of the language.

>      I wish that there was something like #include and #define so I
> could define mnemonics for the errors I wish to define.  I have
> looked, but I did not find anything.

It is not available in the product as such, but you should be able 
to roll your own with help of the preprocessor from C++ if you have
Visual Studio.

As a matter of fact, in the system I spend most of my time with, we
do use a preprocessor. Not the one from C++, but a homebrew. It's
part of my toolset, AbaPerls, which you can find at 
http://www.sommarskog.se/AbaPerls/index.html. But you would not 
start using AbaPerls only to get a preprocessor.

>      That is good in general, but I have a gotcha.  <sigh>  I will not
> be able to use it for the company that the main app is for.  They will
> stick with Windows XP for quite some time.  SS 2011 will not run on
> XP.
 
What can I say... The 64-bit support for XP is not very good, and
there are a few things missing. But not much. And I much rather
do my daily chores on XP than on Vista or Win7.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web