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
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
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
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.
That’s it for now,
It’s Just A Thought … 
