Sifiso Ndlovu

Background to exception handling in SQL Server

June 26, 2014 by
Since the advent of SQL Server 7.0, the best way of raising T-SQL related errors back to calling applications has been through the usage of the RAISERROR statement. Figure 1 demonstrates a combination of mandatory parameters (i.e. msg_id/msg_str, severity, state) as well as optional parameters (i.e. WITH option) that ought to be provided for the purposes of successfully using the RAISERROR function.

Figure 1
Figure showing a combination of parameters for successful usage of RAISERROR function

The content of an exception message raised through the RAISERROR statement can either be hard-coded or retrieved from the sys.messages catalog view based on a corresponding error code number:

  1. RAISERROR: Re-throwing Hard-Coded Exceptions

    An example of declaring and raising a user-defined error message within a RAISERROR statement is illustrated in Figure 2. In this example, a SQL Server stored procedure is created under the sample AdventureWorks2012 database that, amongst other things, uses a local variable (@StringVariable) in order to declare and set a custom message to handle an exception of dividing by zero.

    Figure 2
    Figure illustrating an example of a stored procedure

    Now, for the purposes of simulating an application calling the stored procedure created in Figure 2, Figure 3 shows a SQL Server 2012 Integration Services (SSIS) Package that uses an SSIS’s Execute SQL Task (EST) – titled RAISERROR Customer Error Messages – to execute a stored procedure that was created in Figure 2.

    Figure 3
    Figure showing a SQL Server 2012 Integration Services Package that uses an SSIS’s Execute SQL Task

    The execution of the EST leads to an error and the error message returned to SSIS is shown in Figure 4. It can be noticed that the user-defined custom error message is returned to SSIS as a calling application.

    Figure 4
    Figure illustrating an error message returned to SSIS

  2. RAISERROR: Re-throwing sys.messages-stored Exceptions

    Exceptions stored in the sys.messages can be retrieved by referencing ERROR_MESSAGE, ERROR_SEVERITY and ERROR_STATE system functions. Figure 5 demonstrates a stored procedure that set values of system functions to local variables and RAISERROR then references the locally declared variables.

    Figure 5
    Figure showing a stored procedure that set values of system functions to local variables and RAISERROR

    Similarly to an example on re-throwing user-defined exceptions, the stored procedure created in Figure 5 is referenced in an SSIS Package (as shown in Figure 6) and the result of SSIS package execution is displayed in Figure 7.

    Figure 6
    Figure showing how previously created stored procedure is referenced in an SSIS Package

    Figure 7
    Figure displaying the result of SSIS package execution

SQL Server 2012 exception handling using THROW statement

The preceding section demonstrated the prevalent ways of handling exceptions in versions of SQL Server prior to SQL Server 2012. This section focuses on the alternative way to exception handling as of SQL Server 2012 by using the THROW statement. Figure 8 shows the syntax that should be followed in order to successfully make use of the THROW statement.

Figure 8
Figure showing the syntax that should be followed in order to successfully make use of the THROW statement

Noticeably in this syntax is the disappearance of mandatory parameters that dictates the usage of RAISERROR. Thus, the THROW statement is simpler and easier to use compared to RAISERROR as you have an option of not parsing any parameters. For instance, Figure 9 shows a stored procedure that, like the example given in the RAISERROR discussion, attempts to divide by zero. However, unlike the numerous local variables that were used in the RAISERROR discussion for catching exceptions, Figure 9 simply shows that it only takes a single line within the CATCH block to handle exceptions when the THROW statement is used.

Figure 9
Figure illustrating that it only takes a single line within the CATCH block to handle exceptions when the THROW statement is used

Furthermore, when the stored procedure created in Figure 9 is used in an SSIS Package (as shown in Figure 10), the exception returned looks similar to the exception that was returned when RAISERROR referenced the sys.messages object (as shown in Figure 11) – except of course that it took less lines of code to achieve this using THROW than it did whilst using RAISERROR.

Figure 10
Figure showing using the previously created stored procedure in an SSIS package

Figure 11
Figure showing that the returned exception looks similar to the exception that was returned when RAISERROR referenced the sys.messages object

THROW vs RAISERROR: Different approaches towars SQL Server 2012 exception handling

The preceding section indicated that there is ultimately a simpler and convenient way of re-throwing exceptions when THROW statement is used as compared to RAISERROR. However, the examples demonstrated in preceding sections should not justify an absolute replacement of any T-SQL code that references RAISERROR with a THROW statement. Instead, RAISERROR can still be utilised for some of the requirements that the THROW can’t fulfil.

In this section we will take a closer look at the differences between RAISERROR versus THROW statements. Despite the two statements being used for re-throwing exceptions to calling applications, they tend to behave differently in the way they go about re-throwing exceptions. One significant difference is in the way they handle user exceptions (i.e. exceptions with error codes of 50000 or more) versus system exceptions (i.e. exceptions with error codes less than 50000). Below is a list of points that I have put together to persuade you that in some T-SQL scripts one statement maybe suitable over the other:

  1. Accurate Troubleshooting by Error Line Number

    One of the known issues of raising an error using RAISERROR statement is often the incorrect error line number that is returned to the calling application. Noticeably, this incorrect error line number occurs when RAISERROR statement throws a user-defined exception. For instance, refer to Figure 12 whereby after the T-SQL code is executed – the error line number is given to be at 13. However, this is incorrect as the statement that causes a division by zero is actually at line number 2. This is correctly depicted in Figure 13 wherein the THROW statement is used.

    Figure 12
    Figure showing that the error line number is given to be at line 13

    Figure 13
    Figure illustrating that the error line number is given to be at line 2

  2. Accurate Troubleshooting by Error Number

    Another discrepancy that has largely been attributed to the RAISERROR statement is the incorrect error number that is sometimes returned to calling applications. For instance, according to the sys.messages object, the message_id associated with an error incurred by dividing by zero is 8134 (as shown in Figure 14).

    Figure 14
    The message_id associated with an error incurred by dividing by zero is 8134

    However, the error number returned after the execution of query in Figure 15 is 50000 whereas the query that uses the THROW statement, as shown in Figure 16, is returning the correct error number as 8134.

    Figure 15
    Figure showing that the error number is 5000 for the given query

    Figure 16
    Figure illustrating that the query which uses THROW statement is returning the correct error number as 8134

  3. Accurate Termination of T-SQL Statement

    There are sometimes inconsistencies in terms of whether or not T-SQL commands should be executed after RAISERROR statement is executed. Figure 17 shows that despite the exception having being raised for dividing by zero, ‘Do More’ was still printed afterwards. However, having raised an exception as a result of referencing an invalid column, ApexSQL, in Figure 18 – the statement for printing ‘Do More’ was not executed. Fortunately, such inconsistencies are not tolerated in a THROW statement as it can be seen (in both Figures 19 and 20) that commands that appear after the THROW statement are not executed at all.

    Figure 17
    Figure showing that despite the exception having being raised for dividing by zero, ‘Do More’ was still printed afterwards

    Figure 18
    Figure illustrating how the statement for printing ‘Do More’ was not executed

    Figure 19
    Commands appearing after the THROW statement are not executed at all

    Figure 20
    Commands that appear after the THROW statement are not executed at all

  4. Severity of Exceptions Raised by THROW is Always Set to 16

    SQL Server uses exception severity levels as guide on whether it should return the exception to the client as just a warning or highly critical. Although RAISERROR dynamically returns severity level based on the nature of the exception encountered, the THROW statement invariably returns an exception with a severity level of 16. Figure 21 demonstrates a T-SQL query with conditional logic that throws an exception if the @ColorOfSky variable is not set to Blue. The severity level of this exception is given to be 16. However, the similar T-SQL code in Figures 22 and 23 shows the flexibility of RAISERROR statement in terms of setting and resetting the severity level of an error.

    Figure 21
    An exception is thrown if the @ColorOfSky variable is not set to Blue

    Figure 22
    Using RAISERROR to set/reset the severity level of an error

    Figure 23
    Flexibility of the RAISERROR - customizing the severity level of an error

  5. Unfortunately, the THROW statement lacks support for the WITH argument. In RAISERROR, there is a choice of three possible values that can be used along the WITH argument. These possible values can be:
    1. LOG
    2. NOWAIT
    3. SETERROR

    Figure 24 demonstrates a RAISERROR statement that makes use of the WITH LOG argument to write to Windows Application log. Figure 25 displays the application log with the exception that was raised in Figure 24.

    Figure 24
    RAISERROR statement that makes use of the WITH LOG argument to write to Windows Application log

    Figure 25
    Figure displaying the application log with the exception that was raised in the previous figure

  6. Practice Statement Termination before learning to THROW

    If you are used to getting away with writing T-SQL code without specifying GO or a semi-colon (;) to indicate the end of a statement – then you will find it difficult to adapt to using the THROW statement. Unless you don’t have any statement preceding the THROW statement, you will not get away with using a THROW statement without specifying T-SQL statement terminators. Furthermore, although RAISERROR permits the use of a RAISERROR statement that is preceded by a statement which does not contain T-SQL statement terminators (as shown in Figure 26), such practice is not tolerated in a THROW statement as shown in Figure 27.

    Figure 26
    Using RAISERROR statement preceded by a statement not containing T-SQL statement terminators

    Figure 27
    Figure showing a THROW statement can not be preceded by a statement not containing T-SQL statement terminators

    Figures 28 and 29 demonstrates the acceptable way of using THROW statement with GO and semicolon when it is preceded by other T-SQL statements.

    Figure 28
    Using THROW statement with GO when it is preceded by other T-SQL statements

    Figure 29
    Using THROW statement with semicolon when it is preceded by other T-SQL statements

  7. Be careful what you RAISERROR for

    All user-defined exceptions that do not exist in the sys.messages catalog view are automatically assigned an error number of 50000. However, should you want to assign an exception to any number greater than 50000, the exception would have to be inserted into the sys.messages catalog view prior to being referenced in your RAISERROR statement. Fortunately, the THROW statement does not require that an error number be registered prior to being cited. For instance, Figure 30 shows that error number (message_id) 56789 does not exist in the sys.messages catalog view. Yet, the THROW statement shown in Figure 31 is able to reference a non-existent error number whereas a similar RAISERROR statement is raising an error as shown in Figure 32.

    Figure 30
    Figure showing that error number 56789 does not exist in the sys.messages catalog view

    Figure 31
    THROW statement shown is able to reference a non-existent error number

    Figure 32
    RAISERROR statement is not able to reference a non-existent error number

    Following the successfully registering of the error number (message_id) 56789 in sys.messages catalog view (shown in Figure 33), RAISERROR statement is then able to re-throw an exception with this new error number (as shown in Figure 34).

    Figure 33
    Successful registering of the error number 56789 in sys.messages catalog view

    Figure 34
    RAISERROR statement is able to re-throw an exception with this new error number

  8. It is Always Best to Know What You Can & Can’t THROW

    Although you can raise both user-defined and system-defined exceptions in a RAISERROR statement, system-defined exceptions can only be raised when the THROW statement is used within a CATCH block. For instance, Figure 35 shows that RAISERROR can directly invoke system-defined error number 40645 whereas the exception returned in Figure 36 indicates that only error numbers within the range of 50000 to 2147483647 can be directly invoked in THROW statement.

    Figure 35
    Figure showing that RAISERROR can directly invoke system-defined error number 40645

    Figure 36
    Only error numbers within the range of 50000 to 2147483647 can be directly invoked in THROW statement

    However, error number 8134 is a system exception but notice that (in Figure 37) it can be successfully re-thrown by the THROW statement when the statement is correctly referenced within a CATCH block.

    Figure 37
    Error number 8134 is a system exception but it can be successfully re-thrown by the THROW statement

  9. Handling Substitution Parameters in the Message Parameter

    The error message retrieved from the sys.messages catalog view can be customised by setting message parameter values. In order to for that to happen, part of the error message should contain the printf function. For instance, let’s add a new message in the sys.message object as shown in Figure 38:

    Figure 38
    Adding a new message in the sys.message object

    Now going back to our example of checking for the colour of the sky, Figure 39 demonstrates how RAISERROR statement re-throws a customised message that was added in Figure 38.

    Figure 39
    RAISERROR statement re-throws a customised message

    However, substitution parameters in the message parameter are not allowed in a THROW statement. Luckily, adjustments can be made for the purposes of getting the same code to function within a THROW statement. Such adjustments include the usage of the FORMATMESSAGE function. Figure 40 demonstrates how the work around to using substitution parameters can be applied in a THROW statement.

    Figure 40
    Figure demonstrating how the workaround to using substitution parameters can be applied in a THROW statement

Conclusion

The concept and practice of exception handling in SQL Server is not novel instead statements such as RAISERROR and the TRY/CATCH block has been influential in re-throwing exceptions back to calling applications such as SQL Server Integration Services. The introduction of the THROW statement in SQL Server 2012 has largely made the practice of raising exceptions much more convenient and simple: what used to take a combination of local variables and system functions to successfully re-throw an exception in RAISERROR statement is now easily achievable with a single line when the THROW statement is used. Yet, the THROW statement has various limitations that can only be mitigated by still applying the RAISERROR statement.

Sifiso Ndlovu
168 Views