Implement error handling





  • Error message: a textual representation of the error.

You can write your own custom error messages by using the sp_addmessage stored procedure, and adding the error information as parameters. First you need to write an error message in English, than you can translate it to other languages by specifying the @lang parameter to sp_addmessage.

@@ERROR isn’t the only possible way of dealing with exceptions. SQL Server introduces the well-known TRY/CATCH blocks. You write statements in a TRY block, and if an error occurs (with a severity level between 11 and 19) the CATCH block executes. It is important that severity levels below 11 are informational messages, which are not getting caught, so execution of the TRY section continues. Severity levels above 19 are considered as fatal errors, and they are returned immediately to the caller.

To throw errors, you can use the RAISERROR function, specifying the message, severity level, error number, etc. RAISERROR is particularly useful in CATCH blocks, to rethrow an exception to the caller.

Another thing to mention is the SET XACT_ABORT ON clause. SQL Server doesn’t roll back a transaction which has an error. By specifying SET XACT_ABORT ON you override this behavior, so when an error happens in a transaction, and there are no TRY/CATCH blocks, it will be rolled back. If there are, execution switches to the CATCH block, where you can only roll it back, unfortunately.


Tags: , , , ,

Leave a Reply


You are commenting using your account. Log Out /  Change )

Google photo


Twitter picture


Facebook photo


Connecting to %s

%d bloggers like this: