RAISERROR with TRY … CATCH block

RAISERROR is one of the most efficient method to raise user created exception based on various situations. And TRY CATCH block is perhaps the most known method for exception handling in any programing language. But it is a bit tricky to use both of them together due to manner in which they transfer control to next command.

Following the examples show typical behavior in different cases.

TRY CATCH with Error Severity <= 10. As you know error severity <= 10 are actually informational messages and they don’t raise any error.

 
begin try 
select 'from Try block 1'  
raiserror('Error is Raised' 		  ,10 -- Severity <= 10 		  ,1 		  ,N'number' 		  ,5) with log  
select 'from Try block 2' 
end try  

begin catch 
select 'from catch block' 
end catch  

 

TRY_CATCH_1

Next is TRY CATCH with Error Severity >= 11 and <=19. Now any error severity more than 10 and less than 20 are actually indication of user error, software error and hardware error.

 
begin try 
select 'from Try block 1'  
raiserror ('Error is Raised' 		  ,11 -- Severity >= 11 and <=19 		  ,1 		  ,N'number' 		  ,5) with log  
select 'from Try block 2' 
end try  

begin catch 
select 'from catch block' 
end catch 

 

TRY_CATCH_2

Last is TRY CATCH with Error Severity >=20. This is indication of severe error and when raised connection gets terminated.

 
begin try 
select 'from Try block 1'  
raiserror('Error is Raised' 		  ,20 -- Severity >= 20 		  ,1 		  ,N'number' 		  ,5) with log  
select 'from Try block 2' 
end try  

begin catch 
select 'from catch block' 
end catch 

 

TRY_CATCH_3

So as you can see from above examples that for,

Error Severity <= 10, control doesn’t move to CATCH block.

Error Severity >= 11 and <=19, control moves to CATCH block

Error Severity >= 20, control just gets terminated

Just for curious cats, I am using WITH LOG option to log errors into log but it is ONLY required for error with severity >= 20.

TRY_CATCH_4

That’s it for now,

It’s Just A Thought … Peace

Gaurang Sign

Leave a Reply

Your email address will not be published. Required fields are marked *