RAISERROR

Yesterday i stumbled on a behaviour that i hadn’t thought about before.

This time it was RAISERROR error that surprised me, or it was RAISERROR together with TRY CATCH that surprised me. The problem was that i was running a large SQL script with different kind of data updates, table modifications etc. Somewhere in the script there was a TRY CATCH block with a RAISERROR statement that should have made me aware of errors when executing.

I executed the script and everything seemed fine except the fact that somewhere in the script something had gone wrong and i wasn’t alerted about this.
The reason for me not knowing about the error was that somewhere in the script a RAISERROR was fired inside a CATCH block with severity 10 and that made the script execute successfully.

A closer look in Books Online confirmed what happened: If you use RAISERROR inside a TRY block with the severity of 10 or lower the error is returned to the caller. If, like in my case, you want the error to be returned to the CATCH block you have to raise error with severity 11 or higher.

In my opinion it’s better to better to have Management Studio report back “Query completed with errors” when something goes wrong rather than reporting “Query executed successfully” when in fact it wasn’t.

The full details of RAISERROR can be found here: http://msdn.microsoft.com/en-us/library/ms178592.aspx