CREATE BLOG dbo.SqlServer

If you didn't already know, you can store your own error messages inside SQL Server's sysmessages table (in the master database). This is a handy feature because it allows you to store custom error messages that you can now invoke from any database on the server. In this post I want to show you how you can create your own error messages (using the stored procedure sp_addmessage), how error messages work in SQL Server, and how you can customize your error messages using parameter substitution. Let's see an example:

First, here's just a basic example of how to add a message and then invoke that message:
EXEC master.dbo.sp_addmessage 50001 -- the error number must be higher than 50000
, 16 -- the Severity Level you want for this message (most of the time you'll use 16)
, 'This is the world''s greatest error message!' -- your error

RAISERROR (50001, 16, 1 ) -- the "1" is the State and is mostly unused

You can make your error messages even more dynamic by using parameters in the error message string. Parameters in SQL Server error messages generally will use the %s marker (string parameter) for easier formatting and are very easy to use and retrieve. One thing: you cannot pass functions into the message when you call it therefore you must declare local variables, store your function's results in these variables, and then pass the variables into the message call.

-- EXEC sp_dropmessage 70859 -- removes the message from the system

IF NOT EXISTS ( SELECT * FROM master.dbo..sysmessages WHERE error = 70859)
-- This line adds a new error message to the database. It accepts four string parameters (all denoted by the "%s")
EXEC sp_addmessage 70859, 16, 'The user - %s - was trying to do something really horrible in the %s database on %s at %s. Go punch them in the arm and tell them to stop!'
GO
-- To call this error message, use RAISERROR:
DECLARE @UserName NVARCHAR(128), @DatabaseName NVARCHAR(128), @CurrentDate VARCHAR(20), @CurrentTime CHAR(5)

-- You must declare local variables to store the results of functions:
SELECT @UserName = USER_NAME(), @DatabaseName=DB_NAME(), @CurrentDate = CONVERT(VARCHAR(20), GETDATE(), 101)
, @CurrentTime = CONVERT(CHAR(5), GETDATE(), 8)

-- For each "%s" in the error message, pass in a variable
RAISERROR (70859, 16, 1, @UserName, @DatabaseName, @CurrentDate, @CurrentTime)

In a future post I'll show you how to store multiple versions of the same error message so that your error message can be used in multiple languages. Stay tuned!