Stored Procedures (Part 2)

Good afternoon everyone! Today's blog post is a continuation of Stored Procedures (Part 1), which I would strongly recommend on reading before reading this one as it will give some more background knowledge before we begin.

As mentioned yesterday, we will be tackling error messages, return codes and error handling.

Error Messages

Error messages in SQL Server appear in the Messages tab at the bottom and consist of 3 parts as highlighted below:

• Error Number
• Severity Level
• Error Message

Error Number (or Return Codes) 

An error number is given to every error that SQL Server produces. It is simply an integer ID number (similar to a return code you may receive on a standard Windows error) which makes it easier to search for a solution. Error messages that come with SQL Server are numbered from 1 to 49999, however number 50000 is reserved for an unspecified error message and you can create your own custom messages that take numbers 50001 and above.

Severity Level

SQL Server assigns a severity level (from 0 to 25 inclusive) to any error that occurs. Each number is included within a band:

• Error level 16+ is logged in the SQL Server error log. 
• Error level 19-25 can only be accessed by members of the sysadmin user role. 
• Error level 20-25 are considered fatal errors and any transactions are rolled back. 

Error Message

You can view all messages available to be displayed to the user by typing in the following command in the Query Window:






The error message is the sentence displayed to the user to explain what the error was.

Custom Error Messages

As said previously, you can create your own custom error messages, which take error code numbers from 50001 and above. To create a custom error message, you can execute the following code:

It is also possible to add the error message to its own language settings, like in the example below:

Error Handling Within Stored Procedures

Error handling is incredibly important, regardless of the application type, as it needs to be as user friendly as possible. For example, take a look at the two examples given below: 

Example 1: 










Example 2: 

I'm sure you will agree with me when I say that Example 2 is a much more useful error message as it explains what the problem is and provides an error code as well. As a user, I would prefer the second example any day. Therefore, it is vital to include error trapping methods, not only in the application itself but also in the stored procedures within the database, in the off chance that something may not be working as it should be. You could use XACT_ABORT at the start of your transactions as it will break out and roll back if a error occurs however, it can work with unpredictable results and is not very user friendly. It is much better to use a TRY ... CATCH block, something which you may be familiar with as it features in many other programming languages. 

TRY ... CATCH

During my commercial experience, I have used TRY ... CATCH blocks in C# and the concept is really very similar in SQL (as the flow chart diagram below shows):

However you need to consider:

• The CATCH block must follow the TRY block immediately.
• TRY ... CATCH blocks can be nested within each other. 
• If there is an error within the CATCH block, it will be returned to the application unless it is nested within another TRY ... CATCH block. 
• Transactions can be either committed or rolled back, depending on whether the error is in a committable state.
• You can use the following functions to pass back to the application user: 
• ERROR_NUMBER() - Displays the error number. 
• ERROR_MESSAGE() - Displays the error message.
• ERROR_SEVERITY() - Displays the error severity.
• ERROR_STATE() - Displays the state of the error:
• 1: An open transaction that can be committed or rolled back. 
• 0: No open transaction.
• -1: An open transaction that suffered a fatal error and can only be rolled back. 
• ERROR_PROCEDURE() - Displays the name of the procedure that triggered the error to occur.
• ERROR_LINE() - Displays the line of code where the error occurred. 
• No error messages can be sent back to the application itself unless a RAISERROR command is executed within the CATCH block.

RAISERROR

The RAISERROR command can be used in both the TRY and the CATCH blocks of a TRY... CATCH statement.

• If an error with severity less than 10 occurs, a warning message is sent back to the application and the CATCH block is not called. 
• If an error with severity between (and including) 11 and 19 occurs and the RAISERROR is in the TRY block, control is passed to the corresponding CATCH block. 
• If an error with severity between (and including) 11 and 19 occurs and the RAISERROR is in the CATCH block , the error is returned back to the application.
•  If an error with a severity greater than 20 occurs, the connection to the database server is terminated and the CATCH block is not called. 

This is very important to keep in mind when trying to determine what your error message is going to do.

I will now use a quick example to show the structure of a TRY...CATCH block. The first image below shows my transaction: 

When I execute the query above, it does the SELECT statement and returns a standard SQL error message: 

However, this may not be suitable to be sent back to the application user. The image below shows an example of a TRY ... CATCH block around my transaction: 

The message can be completely customised within the print section and will display in exactly the same place as the other error, just featuring your text instead.

Now, if I wanted to send this error message back to the main application, I would replace the PRINT with RAISERROR (note that you will need to pass the ERROR_SEVERITY and then pass the ERROR_STATE):

To Be Continued...

Part 3 will focus on: 

• The safe execution of Stored Procedures. Stay tuned!