Published on

October 4, 2005

Using RAISERROR in SQL Server

Have you ever encountered a situation where a stored procedure in your SQL Server database seems to be misbehaving? Maybe it’s returning unexpected records or freezing without completing. In such cases, it can be challenging to pinpoint the exact issue and prove that it’s the way the application calls the stored procedure that’s at fault.

In this article, we’ll explore a simple approach to troubleshooting stored procedures using the RAISERROR command. RAISERROR allows you to print out parameters and variables at key stages of the procedure, helping you identify any issues in the application’s interaction with the stored procedure.

There are three common ways to print out values in SQL Server: SELECT statements, PRINT statements, and RAISERROR statements. While SELECT and PRINT statements have their limitations, RAISERROR provides a more flexible and efficient solution.

RAISERROR statements allow you to print multiple values of different types and can even echo values into the Windows Event Log. This is particularly useful as it shifts message logging from the application to the stored procedure, simplifying the debugging process.

Here’s an example of how you can use the RAISERROR statement to write a message directly to the Windows Event Log:

DECLARE @sStringVar VARCHAR(440),
        @lIntegerInt,
        @fFloatdecimal(6,2),
        @byUnsignedInttinyint

SELECT @sStringVar = 'A string',
       @lInteger = 44,
       @fFloat = 3.14,
       @byUnsignedInt = 56

RAISERROR('The string variable contains %s, the int contains %d, the float contains %d, the uint contains %d',
          10, -- Informational severity only
          1,
          @sStringVar,
          @lInteger,
          @fFloat,
          @byUnsignedInt
) WITH LOG, NOWAIT

The crucial statements in the RAISERROR command are the WITH LOG and NOWAIT clauses. The LOG option causes the message to be echoed into the Windows Event Log, providing a centralized location for message logging. The NOWAIT option ensures that the message is echoed immediately when the statement is run, rather than waiting for the batch to complete.

While using RAISERROR statements, there are a few things to keep in mind:

  • Message size is limited to 440 characters, which is usually sufficient for most scenarios.
  • REAL, Float, and DATETIME values are not allowed directly, but they can be cast to strings using the CAST or CONVERT statements.
  • The number and type of arguments must match the number and type of placeholders in the message.

RAISERROR also provides some useful tricks for formatting the output:

  • %+d will display an integer with the correct sign (+ or -).
  • %6d creates a space 6 characters wide to hold the value, right-justified.
  • %-6d creates a space 6 characters wide but left-justifies the value.
  • %x and %X render the value in hexadecimal, with lowercase and uppercase letters respectively.
  • %#x and %#X render the hexadecimal value in lowercase and uppercase respectively, preceded by “0x”.
  • %u displays int values of 0 to 2147483647 as is, and -1 to -21474748348 as 4294967295 to 21474748348, representing the 2’s complement value.

Real-world example:

Let’s say you have a stored procedure with multiple SQL statements, and you’re trying to identify which statement block is causing a lock. By using RAISERROR statements strategically, you can track the execution of each statement block and measure the duration of each block. This can help you identify the problematic block and gain insights into resource consumption.

DECLARE @sMessage VARCHAR(440)

-- Declare a variable for the message
DECLARE @sMessage VARCHAR(440)

-- Place RAISERROR statements around each statement block
SET @sMessage = CONVERT(CHAR(19), GETDATE(), 120) + ' : started'
RAISERROR(@sMessage, 10, 1) WITH LOG, NOWAIT

...

SET @sMessage = CONVERT(CHAR(19), GETDATE(), 120) + ' : completed in %d milliseconds'
RAISERROR(@sMessage, 10, 1, @lDuration) WITH LOG, NOWAIT

By examining the paired messages in the event log, you can determine the start and completion times of each statement block, as well as the duration in milliseconds. This information can help you identify the blocks that are taking the longest to run and potentially consuming the most resources.

In conclusion, the RAISERROR command is a powerful tool for troubleshooting stored procedures in SQL Server. By strategically placing RAISERROR statements and utilizing its various formatting options, you can gain valuable insights into the execution flow and performance of your procedures. This can greatly simplify the debugging process and help you identify and resolve issues more efficiently.

Click to rate this post!
[Total: 0 Average: 0]

Let's work together

Send us a message or book free introductory meeting with us using button below.