Path: csiph.com!x330-a1.tempe.blueboxinc.net!usenet.pasdenom.info!news.albasani.net!eternal-september.org!feeder.eternal-september.org!.POSTED!not-for-mail
From: Gene Wirchenko <genew@ocis.net>
Newsgroups: comp.databases.ms-sqlserver,microsoft.public.sqlserver.programming
Subject: Re: SSE 2008: Transactions and Rollbacks: When are they done?
Date: Thu, 19 May 2011 23:20:51 -0700
Organization: A noiseless patient Spider
Lines: 73
Message-ID: <0g1ct6hmqe2hs0djmofsgi7bami29mc16b@4ax.com>
References: <91q5t6h56vk3db1ipt8eacd5407jc08c7f@4ax.com> <Xns9EE9602FD7B17Yazorman@127.0.0.1> <9618t6ta7j80ptb76ko10h873g28v5dsmf@4ax.com> <Xns9EE9F09B4267EYazorman@127.0.0.1> <91uat6d92cg68lk6472coa3okuque54tde@4ax.com> <Xns9EEAF07EABDAFYazorman@127.0.0.1>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Injection-Info: mx04.eternal-september.org; posting-host="7Qrvczazr82YckO5XW8Vtw"; logging-data="29476"; mail-complaints-to="abuse@eternal-september.org";	posting-account="U2FsdGVkX19E1tVyQNw6seCW5CgCfFr8NrQlNCRTpuQ="
X-Newsreader: Forte Agent 4.2/32.1118
Cancel-Lock: sha1:UN4qLId8HxWashz3wfn4ZhXj8W0=
Xref: x330-a1.tempe.blueboxinc.net comp.databases.ms-sqlserver:360

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