SQL 2005+

Raising Errors in Transact-SQL

by Richard Carr, published at http://www.blackwasp.co.uk/SQLRaiserror.aspx

When developing reusable scripts, such as stored procedures and triggers, it is often necessary to raise errors. With the RAISERROR command, it is possible to create errors with varying severity and customised messages.

Reserving Space in the Message

It can be useful to specify a minimum width for a placeholder's content, essentially reserving space for the inserted value. To do so, specify the number of characters between the hash and the placeholder code letter. If the value is shorter than the minimum size, spaces will be inserted before it. This can be used to right-align values:

DECLARE @Number1 INT
DECLARE @Number2 INT

SET @Number1 = 42
SET @Number2 = 999

RAISERROR('Error:
%5u
%5u', 16, 1, @Number1, @Number2)

/* Message

Error:
   42
  999

*/

To align values to the left, add a minus sign before the number of reserved characters, as in the next example:

DECLARE @Number1 INT
DECLARE @Number2 INT

SET @Number1 = 42
SET @Number2 = 999

RAISERROR('Error: %-5u %-5u', 16, 1, @Number1, @Number2)  -- Error: 42    999

Another useful option when inserting numbers is to pad the value with leading zeroes. To achieve this, insert a zero before the number of characters to reserve, as in the example below:

DECLARE @Number1 INT
DECLARE @Number2 INT

SET @Number1 = 42
SET @Number2 = 999

RAISERROR('Error: %05u %05u', 16, 1, @Number1, @Number2)  -- Error: 00042 00999

Finally, sometimes you will want to vary the minimum width for an inserted value. In these cases, instead of specifying a number, use an asterisk (*). The size of the reserved space can then be set using an argument of the RAISERROR call.

The last example demonstrates this. The fourth and sixth arguments are the two values to be inserted. The fifth argument sets the number of characters to be used in the second insert.

DECLARE @Number1 INT
DECLARE @Number2 INT
DECLARE @Number3 INT

SET @Number1 = 42
SET @Number2 = 6
SET @Number3 = 999

RAISERROR('Error: %05u %0*u', 16, 1, @Number1, @Number2, @Number3)  -- Error: 00042 000999