T-sql error raise

t-sql error raise

THROW. Ad good as the new error-handling functionality was in SQL Server 2005, there were still some pieces missing when comparing t-sql. The RAISERROR statement allows user-defined errors to be signaled, and sends a message on the client. The error-number is a 5-digit integer greater than 17000. If I don't control the higher-level caller, then I (virtually) always capture the error information (and related data) and record it to a.

T-sql error raise - congratulate

SQL Server Error Handling Gotchas

Way back when (in 2010 as a matter of fact), I wrote a couple of blog-posts (here and here) about error handling in the new CTP releases of SQL Server Denali. Denali was what would become SQL Server 2012.

The new functionality built upon what was introduced in SQL Server 2005 - the notion of structured exception handling through followed by . In those blog-posts I was fairly positive, and saw the new functionality as something useful and very well worth implementing. I am still positive, however since then I have used the new functionality introduced in SQL Server 2005 extensively in production and have come across some gotchas that I thought would be worth writing a blog-post about.

Background

Before SQL Server 2005 was introduced, and with that structured exception handling as mentioned above, the way we handled error conditions in SQL Server was to write something like so:

Code Snippet 1:Old Style Error Handling

In your stored procedures, you had to insert code like above after each statement where you wanted to check for errors. Part of the error-handling code quite often would be some logging/auditing and general rewind/cleanup. Typically you would also re-raise the error so that calling procs would be made aware that something has happened, and in the calling procs you would have similar logic to catch the errors being raised.

All in all, quite a lot of code to write. At Derivco we have a lot of stored procedures, and they can be fairly big (3000 - 4000 loc), so you can imagine the number of error checks we have in them.

TRY CATCH

Writing the above mentioned error-handling code feels quite arcane if you compare what you would do in other programming languages. So in SQL Server 2005 Microsoft introduced the notion of structured exception handling as I mentioned above, and it was implemented through and blocks. As with other programming languages you can have one or more TRY … CATCH blocks, and when an error happens inside the TRY block, the code in the CATCH block is immediately hit.

Code Snippet 2:Error Handling with TRY … CATCH

When the block together with executes, it creates a special error context, so that any errors happening will be caught by the closest CATCH block. In other words, errors “bubble” up to the closest CATCH block, within the same SPID. Keep this in mind, as it is important when we discuss some of the “gotcha’s”.

THROW

Ad good as the new error-handling functionality was in SQL Server 2005, there were still some pieces missing when comparing t-sql with other languages error-handling. One big glaring missing piece was how to re-throw a caught exception. What you typically would do if you wanted to re-throw was to capture the error text, either from pre SQL Server 2005, or from in SQL SERVER 2005+, and in both cases then use the captured text in .

Note about :

  • It allows you to throw an error based on either an error number or a message, and you can define the severity level and state of that error.
  • If you call with an error number, that error number has to exist in sys.messages.
  • You can use error numbers between 13001 and 2147483647 (it cannot be 50000) with .

has been around “forever”, and for SQL Server 2012 Microsoft introduced as new function to be used when raising exceptions. Some of the features of :

  • can be used to re-throw an exception.
  • Using you can throw a specific error number together with an accompanying message.

Code snippet 3 below shows an example of this:

Code Snippet 3:Error Handling with THROW and TRY … CATCH

If you have managed to stay awake until now, you probably wonder where is the problem with all this, this looks pretty sweet, or as we use to say in the team I work for in Derivco; “What could possibly go wrong?”! Whenever we say that, it seems that quite a few things can go wrong :) and the same thing holds true here, as we will see!

The Problem

The problem comes in when you are mixing “old” (pre 2005), with “new” (2005+) error handling. You may ask: “why would you ever want to do that, why not use only the new cool features?”. In fact that’s what I asked when I visited Derivco back in 2009 and taught a Developmentor SQL Server course for the team I eventually would work for. Boy was that a stupid question!

The answer - in Derivco’s’ case - is complexity. In our main OLTP production database we now have ~5,000 stored procedures, and the call stacks can nest 10 - 15 procedures deep. In addition, our procedures are not simple CRUD, but we do have LOADS of business logic in them. So you cherry-pick what procs to edit, most likely some you are changing anyway, and all new procs are written using the new error-handling. What could possibly go wrong with this approach?!

Well, chances are that the new/edited procs are part of a call chain, and not necessarily the last proc in the chain. This is now the situation where issues can happen. Let’s look at this a bit closer. In the demo code for this post, we have initially four procedures:

  • which calls into
  • which calls into
  • which calls into
  • which is the last proc in the chain.

The three first procs pr_Caller, pr_1 and pr_2 looks almost identical, and I let be the “showcase”:

Code Snippet 4:Example of Calling Proc

The last proc in the chain - - is somewhat different in that it generates an error:

Code Snippet 5:Error Proc

When you look at the procs you can see that they all use the old style error handling, and are doing clean-ups etc in the block. If you execute the calling proc: , the result in the Message tab in SQL Server Management Studio (SSMS) would look something like:

Figure 1:Output from Error Procs

From the figure above we can see:

  • we are calling into each proc: We are now about to execute.
  • the division by 0 error happens: Divide by zero error encountered.
  • each proc in the call chain cleaning up, etc.: We are cleaning up ….

This is good (well not good that we are getting an error), but we are handling it and cleaning up after ourselves. We may perhaps write the errors to some logging tables, so that we in case of un-expected behavior can trace and see what has happened.

Let us now assume that we need to change , to add some new functionality, whatever. This is now a good time to alter this old proc to use the new “cool” error-handling:

Code Snippet 6:Edited Proc

No problem with the changes, however when you execute you get following result:

Figure 2:Output from Altered Error Procs

Where is the cleanup in and , as an error clearly has happened as something was caught in ? Oh, and what happened with the error-handling in , we did raise an error in ?

The last question is the easiest to answer, and fix; if you want old style error-handling to be able to catch an error raised from within a CATCH block, the MUST be followed by a , and it has to be a with no variables! So change the CATCH block in to:

Code Snippet 7:Use RETURN After Raising an Exception

If you after the above change were to you would see how pr_Caller would handle the error raised in pr_1 as well. The first issue which arguably is more severe is easy to answer; it has to do with that “error context” mentioned in the beginning.

Error Context (a.k.a “Go Straight to CATCH Without Passing IF(@@ERROR”)

As I mentioned in the beginning, when a stored procedure is executed, and during the execution it comes across block(s), a special execution context(s) is created from the point of the first . This context “wraps” all code from that point on, and ensures that if an error happens, the execution will stop and the closest block will be hit. That is the reason why the cleanup code in neither nor was executed.

The answer was easy, but the fix is neither easy nor straightforward. The only way (I am aware of) is that if you edit/create a new proc using the new way of handling errors, you need to ensure that the whole call-stack from that way onward is also using the new way.

THROW

Finally (pun intended), let’s discuss , as so far we have not seen any traces of it in the code. Let us edit to use new error handling as well as using to re-throw an exception:

At the same time in the error handling code of , let’s select out the error message as well as error number, right before we raise the exception: , and then . All should be as before, and in the Results tab in SSMS you should see:

Figure 3:Correct Error Number and Message

By receiving 8134 as error number we can see that actually does what it is supposed to. However what happens if we were to edit to also , seeing that is still doing old style error handling:

Execute the pr_Caller, and notice the output: there is nothing there from dbo.pr_Caller. If is used down in the call chain somewhere, there has to be a calling proc using the new error handling!

Summary

So in summary:

  • TRY CATCH blocks ARE good!
  • However, be careful when mixing new TRY CATCH with “old” @@ERROR
  • You need to ensure all nested procedures called inside the TRY block is also using TRY CATCH.
  • If raising an error in a CATCH block, ALWAYS follow the RAISERROR with a RETURN (no value).
  • Unless you can guarantee that your code will always use TRY CATCH, stay away from THROW.

Download the demo code from here!!


Blog Feed:

To automatically receive more posts like this, please subscribe to my RSS/Atom feedin your feed reader!

Testing, Testing, Anyone Out There?Abort, Abort, We Are XACT_ABORT:ing, Or Are We?!

comments powered by
l}]] type

The parameters that can be used in msg_str are:

flag

A code that determines the spacing and justification of the substituted value.

CodePrefix or justificationDescription
- (minus)Left-justifiedLeft-justify the argument value within the given field width.
+ (plus)Sign prefixPreface the argument value with a plus (+) or minus (-) if the value is of a signed type.
0 (zero)Zero paddingPreface the output with zeros until the minimum width is reached. When 0 and the minus sign (-) appear, 0 is ignored.
# (number)0x prefix for hexadecimal type of x or XWhen used with the o, x, or X format, the number sign (#) flag prefaces any nonzero value with 0, 0x, or 0X, respectively. When d, i, or u are prefaced by the number sign (#) flag, the flag is ignored.
' ' (blank)Space paddingPreface the output value with blank spaces if the value is signed and positive. This is ignored when included with the plus sign (+) flag.

width

An integer that defines the minimum width for the field into which the argument value is placed. If the length of the argument value is equal to or longer than width, the value is printed with no padding. If the value is shorter than width, the value is padded to the length specified in width.

An asterisk (*) means that the width is specified by the associated argument in the argument list, which must be an integer value.

precision

The maximum number of characters taken from the argument value for string values. For example, if a string has five characters and precision is 3, only the first three characters of the string value are used.

For integer values, precision is the minimum number of digits printed.

An asterisk (*) means that the precision is specified by the associated argument in the argument list, which must be an integer value.

{h

RAISERROR statement [T-SQL]

Description

Signals an error and sends a message to the client.

Syntax

RAISERRORerror-numberformat-string ] [, arg-list ]

Examples

Example 1

Raises error 99999, which is in the range for user-defined errors, and sends a message to the client:

RAISERROR 99999 'Invalid entry for this column: %1!', @val

There is no comma between the error-number and the format-string parameters. The first item following a comma is interpreted as the first item in the argument list.

Usage

The RAISERROR statement allows user-defined errors to be signaled, and sends a message on the client.

Theerror-number is a 5-digit integer greater than 17000. The error number is stored in the global variable @@error.

Ifformat-string is not supplied or is empty, the error number is used to locate an error message in the system tables. Adaptive Server Enterprise obtains messages 17000-19999 from the SYSMESSAGES table. In Sybase IQ, this table is an empty view, so errors in this range should provide a format string. Messages for error numbers of 20000 or greater are obtained from the SYS.SYSUSERMESSAGES table.

The format-string can be up to 255 bytes long. This is the same as in Adaptive Server Enterprise.

The extended values supported by the SQL Server or Adaptive Server Enterprise RAISERROR statement are not supported in Sybase IQ.

The format string can contain placeholders for the arguments in the optional argument list. These placeholders are of the form %nn!, where nn is an integer between 1 and 20.

Intermediate RAISERROR status and code information is lost after the procedure terminates. If at return time an error occurs along with the RAISERROR then the error information is returned and the RAISERROR information is lost. The application can query intermediate RAISERROR statuses by examining @@error global variable at different execution points.


Side effects

None

Standards

  • SQL Transact-SQL extension to ISO/ANSI SQL grammar.

  • Sybase Supported by Adaptive Server Enterprise.

Permissions

Must be connected to the database.

See also

CONTINUE_AFTER_RAISERROR option [TSQL]

ON_TSQL_ERROR option [TSQL]

THROW (Transact-SQL)

  • Article
  • 3 minutes to read

Applies to: SQL Server (all supported versions) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Raises an exception and transfers execution to a CATCH block of a TRY...CATCH construct.

Topic link iconTransact-SQL Syntax Conventions

Syntax

Arguments

error_number
Is a constant or variable that represents the exception. error_number is int and must be greater than or equal to 50000 and less than or equal to 2147483647.

message
Is a string or variable that describes the exception. message is nvarchar(2048).

state
Is a constant or variable between 0 and 255 that indicates the state to associate with the message. state is tinyint.

The statement before the THROW statement must be followed by the semicolon (;) statement terminator.

If a TRY...CATCH construct is not available, the statement batch is terminated. The line number and procedure where the exception is raised are set. The severity is set to 16.

If the THROW statement is specified without parameters, it must appear inside a CATCH block. This causes the caught exception to be raised. Any error that occurs in a THROW statement causes the statement batch to be terminated.

% is a reserved character in the message text of a THROW statement and must be escaped. Double the % character to return % as part of the message text, for example 'The increase exceeded 15%% of the original value.'

Differences Between RAISERROR and THROW

The following table lists differences between the RAISERROR and THROW statements.

RAISERROR statementTHROW statement
If a msg_id is passed to RAISERROR, the ID must be defined in sys.messages.The error_number parameter does not have to be defined in sys.messages.
The msg_str parameter can contain printf formatting styles.The message parameter does not accept printf style formatting.
The severity parameter specifies the severity of the exception.There is no severity parameter. When THROW is used to initiate the exception, the severity is always set to 16. However, when THROW is used to re-throw an existing exception, the severity is set to that exception's severity level.
Does not honor SET XACT_ABORT.Transactions will be rolled back if SET XACT_ABORT is ON.

Examples

A. Use THROW to raise an exception

The following example shows how to use the statement to raise an exception.

Here is the result set.

B. Use THROW to raise an exception again

The following example shows how to use the statement to raise the last thrown exception again.

Here is the result set.

C. Use FORMATMESSAGE with THROW

The following example shows how to use the function with to throw a customized error message. The example first creates a user-defined error message by using . Because the THROW statement does not allow for substitution parameters in the message parameter in the way that RAISERROR does, the FORMATMESSAGE function is used to pass the three parameter values expected by error message 60000.

Here is the result set.

Next steps

Learn more about related concepts in the following articles:

l} type

Used with character types d, i, o, s, x, X, or u, and creates shortint (h) or longint (l) values.

Type specificationRepresents
d or iSigned integer
oUnsigned octal
sString
uUnsigned integer
x or XUnsigned hexadecimal

These type specifications are based on the ones originally defined for the function in the C standard library. The type specifications used in message strings map to Transact-SQL data types, while the specifications used in map to C language data types. Type specifications used in are not supported by when Transact-SQL does not have a data type similar to the associated C data type. For example, the %p specification for pointers is not supported in because Transact-SQL does not have a pointer data type.

To convert a value to the Transact-SQL bigint data type, specify %I64d.

@local_variable

Is a variable of any valid character data type that contains a string formatted in the same manner as msg_str. @local_variable must be char or varchar, or be able to be implicitly converted to these data types.

severity

The user-defined severity level associated with this message. When using msg_id to raise a user-defined message created using , the severity specified on overrides the severity specified in .

For severity levels from 19 through 25, the WITH LOG option is required. Severity levels less than 0 are interpreted as 0. Severity levels greater than 25 are interpreted as 25.

Caution

Severity levels from 20 through 25 are considered fatal. If a fatal severity level is encountered, the client connection is terminated after receiving the message, and the error is logged in the error and application logs.

You can specify to return the severity value associated with the error as shown in the following example.

Here is the result set.

state

An integer from 0 through 255. Negative values default to 1. Values larger than 255 should not be used.

If the same user-defined error is raised at multiple locations, using a unique state number for each location can help find which section of code is raising the errors.

argument

The parameters used in the substitution for variables defined in msg_str or the message corresponding to msg_id. There can be 0 or more substitution parameters, but the total number of substitution parameters cannot exceed 20. Each substitution parameter can be a local variable or any of these data types: tinyint, smallint, int, char, varchar, nchar, nvarchar, binary, or varbinary. No other data types are supported.

option

A custom option for the error and can be one of the values in the following table.

ValueDescription
Logs the error in the error log and the application log for the instance of the Microsoft SQL Server Database Engine. Errors logged in the error log are currently limited to a maximum of 440 bytes. Only a member of the sysadmin fixed server role or a user with ALTER TRACE permissions can specify WITH LOG.

Applies to: SQL Server
Sends messages immediately to the client.

Applies to: SQL Server, SQL Database
Sets the and values to msg_id or 50000, regardless of the severity level.

Applies to: SQL Server, SQL Database

The errors generated by operate the same as errors generated by the Database Engine code. The values specified by are reported by the , , , , , , and system functions. When is run with a severity of 11 or higher in a TRY block, it transfers control to the associated block. The error is returned to the caller if is run:

  • Outside the scope of any block.

  • With a severity of 10 or lower in a block.

  • With a severity of 20 or higher that terminates the database connection.

blocks can use to rethrow the error that invoked the block by using system functions such as and to retrieve the original error information. is set to 0 by default for messages with a severity from 1 through 10.

When msg_id specifies a user-defined message available from the sys.messages catalog view, processes the message from the text column using the same rules as are applied to the text of a user-defined message specified using msg_str. The user-defined message text can contain conversion specifications, and will map argument values into the conversion specifications. Use to add user-defined error messages and to delete user-defined error messages.

can be used as an alternative to PRINT to return messages to calling applications. supports character substitution similar to the functionality of the function in the C standard library, while the Transact-SQL statement does not. The statement is not affected by blocks, while a run with a severity of 11 to 19 in a TRY block transfers control to the associated block. Specify a severity of 10 or lower to use to return a message from a block without invoking the block.

Typically, successive arguments replace successive conversion specifications; the first argument replaces the first conversion specification, the second argument replaces the second conversion specification, and so on. For example, in the following statement, the first argument of replaces the first conversion specification of ; and the second argument of replaces the second conversion specification of

If an asterisk () is specified for either the width or precision of a conversion specification, the value to be used for the width or precision is specified as an integer argument value. In this case, one conversion specification can use up to three arguments, one each for the width, precision, and substitution value.

For example, both of the following statements return the same string. One specifies the width and precision values in the argument list; the other specifies them in the conversion specification.

Permissions

Severity levels from 0 through 18 can be specified by any user. Severity levels from 19 through 25 can only be specified by members of the sysadmin fixed server role or users with ALTER TRACE permissions.

Examples

A. Returning error information from a CATCH block

The following code example shows how to use inside a block to cause execution to jump to the associated block. It also shows how to use to return information about the error that invoked the block.

Note

only generates errors with state from 1 through 127. Because the Database Engine may raise errors with state 0, we recommend that you check the error state returned by ERROR_STATE before passing it as a value to the state parameter of .

B. Creating an ad hoc message in sys.messages

The following example shows how to raise a message stored in the sys.messages catalog view. The message was added to the sys.messages catalog view by using the system stored procedure as message number .

C. Using a local variable to supply the message text

The following code example shows how to use a local variable to supply the message text for a statement.

See also