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

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

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

Cross-posted to 2 groups.

Show all headers | View raw

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

Back to comp.databases.ms-sqlserver | Previous | Next — Next in thread | Find similar

Thread

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

csiph-web