XPost: microsoft.public.sqlserver.programming   
   From: genew@ocis.net   
      
   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   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)