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

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

Re: SSE 2008: Transactions and Rollbacks: When are they done?

Cross-posted to 2 groups.

Show all headers | View raw

On Thu, 19 May 2011 23:38:29 +0200, Erland Sommarskog
<esquel@sommarskog.se> wrote:

>Gene Wirchenko (genew@ocis.net) writes:
>>      It does not say why it normally does.  I think the reason is that
>> a raiserror() with a severity >=11 triggers the catch block, but it is
>> not documented (that I can see).
> 
>RAISERROR should invokes the CATCH block, so much is right. This is
>applies to both triggers and regular code.

     Nope.  If the severity is <=10, then raiserror() will not result
in the catch block triggering.  This is documented.  I do not know why
it is this way, but it is.

     Try the following code.  Besides labelling, the only difference
between the two procedures is the severity in the raiserror().

***** Start of Code *****
use tempdb
go

if object_id(N'HighSeverity',N'P') is not null
   drop procedure HighSeverity
if object_id(N'LowSeverity',N'P') is not null
   drop procedure LowSeverity
go

create procedure HighSeverity
as
   begin try
   print N'In HighSeverity'
   raiserror(N'This is a severe error.',11,1)
   print N'Catch did not trigger.'
   end try
   begin catch
   print N'Catch triggered.'
   end catch

go

create procedure LowSeverity
as
   begin try
   print N'In LowSeverity'
   raiserror(N'This is a mild error.',10,1)
   print N'Catch did not trigger.'
   end try
   begin catch
   print N'Catch triggered.'
   end catch

go

execute HighSeverity
execute LowSeverity
***** End of Code *****

     The output will be:

In HighSeverity
Catch triggered.
In LowSeverity
This is a mild error.
Catch did not trigger.

>As for what is documented or not is this madhouse I don't know. I only
>know that the updated version of my article on error handling is long
>overdue...

Sincerely,

Gene Wirchenko

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

Thread

SSE 2008: Transactions and Rollbacks: When are they done? Gene Wirchenko <genew@ocis.net> - 2011-05-17 15:09 -0700
  Re: SSE 2008: Transactions and Rollbacks: When are they done? Erland Sommarskog <esquel@sommarskog.se> - 2011-05-18 07:27 +0000
    Re: SSE 2008: Transactions and Rollbacks: When are they done? Gene Wirchenko <genew@ocis.net> - 2011-05-18 10:50 -0700
      Re: SSE 2008: Transactions and Rollbacks: When are they done? Erland Sommarskog <esquel@sommarskog.se> - 2011-05-18 23:39 +0200
        Re: SSE 2008: Transactions and Rollbacks: When are they done? Gene Wirchenko <genew@ocis.net> - 2011-05-19 13:10 -0700
          Re: SSE 2008: Transactions and Rollbacks: When are they done? Erland Sommarskog <esquel@sommarskog.se> - 2011-05-19 23:38 +0200
            Re: SSE 2008: Transactions and Rollbacks: When are they done? Gene Wirchenko <genew@ocis.net> - 2011-05-19 23:20 -0700
              Re: SSE 2008: Transactions and Rollbacks: When are they done? Erland Sommarskog <esquel@sommarskog.se> - 2011-05-20 07:32 +0000

csiph-web