SQL Server T-SQL User-Defined Function of the Week

Retrieving the User Created System Messages Used by RAISERROR

Volume 1 Number 6   December 23, 2002  

Visit http://www.NovickSoftware.com for additional material
about SQL Server, Visual Basic, ASP, XML, and security.

Visit the T-SQL UDF of the Week at:
http://www.NovickSoftware.com/UdfOfWeek/UDFofWeek.htm

Check out the UDF Frequently Asked Questions at:
http://www.novicksoftware.com/UdfOfWeek/UDF_FAQ.htm

To ask a UDF related question or to contribute a UDF,
send e-mail to udf@NovickSoftware.com

Sign up for this newsletter at:
http://www.novicksoftware.com/UdfOfWeek/UDFofWeekSignup.htm

The RAISERROR SQL statement sets the error state and sends an 
error message back to the caller.  It can be used in a stored 
procedure or trigger but not in a user-Defined function.

The first argument to RAISERROR is either the message text
or a msg_id.  If it's a msg_id it refers to a user defined
message stored in the master..sysmessages table.  User error
messages should be greater than 50,000.

Messages are added to sysmessages with the system stored
procedure sp_addmessage as in this script:

exec sp_addmessage @msgnum=50001, @Severity=11, 
             @msgtext=N'Value is out of range.'
GO
             
This week's UDF, udf_SQL_UserMessages, creates a table of the 
system messages on a SQL Server. I use it as input to the 
report-writer report that I use to document system messages 
for an application.  Here's the CREATE FUNCTION Script:

/------------- Copy From Below this line ----------------------\

CREATE FUNCTION dbo.udf_SQL_UserMessagesTAB ()

    RETURNS TABLE -- Table of messages

AS
/*
* Returns a table of user messages in the SQL Server.
*
* Common Usage:
select ErrorNum, Severity, Message
      FROM dbo.udf_SQL_UserMessagesTAB() 
      WHERE LanguageCode=1033
*
* © Copyright 2002 Andrew Novick http://www.NovickSoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain 
* other unrelated database objects. You may not publish this 
* UDF either in print or electronically.
***************************************************************/

RETURN SELECT TOP 100 Percent WITH TIES
           [Error] as [ErrorNum]
         , Severity
         , msglangid as [LanguageCODE]
         , [description] as Message 
        FROM master..sysmessages
        WHERE ERROR > 50000 -- ONLY USER MESSAGES
        ORDER BY [Error]

GO

GRANT SELECT on dbo.udf_SQL_UserMessagesTAB to PUBLIC
GO

\------------ Stop copying above this line --------------------/


Let's try it out with the sample script:
/------------- Copy From Below this line ----------------------\
SELECT ErrorNum, Severity, Message       
      FROM dbo.udf_SQL_UserMessagesTAB()      
      WHERE LanguageCode=1033                 
GO
\------------ Stop copying above this line --------------------/
(Results - Truncated on the right)
ErrorNum    Severity Message                                     
----------- -------- --------------------------------------------
      50001       11 Value is out of range.                      
      50002       11 Now is the time for all good men to come to 

There is only one sysmessages table per instance of SQL Server.
That means that all databases share the user messages.  If you 
think your database might have to move to another server that's 
shared with other database, be careful how you assign msg_ids.
The same message numbers in your code might be in use by some 
other application. 

If you have to move user messages from one SQL Server to another,
there is a convenient DTS task, Transfer Error Messages, that
will do the job for you.

Seasons Greetings to All!

+--------------------------------------------------------------+
Do you have a great UDF that you'd like to share?  Or maybe you
have a T-SQL problem that you think could be solved by a UDF
but you don't know how? Send them to:

UDF@NovickSoftware.com 

and they might be published in this newsletter.  I try 
and respond to every request that I get.  

Thanks,  
Andrew Novick
+--------------------------------------------------------------+