Customize your message using FORMATMESSAGE Function

Using FormatMessage function you can construct a message string located in sys.messages. The functionality of FORMATMESSAGE is very much same as RAISERROR statement. FORMATMESSAGE functions edits the message by substituting the supplied parameter values for placeholder variables in the message string.

Syntax:

FORMATMESSAGE ( { msg_number  | ‘ msg_string ‘ } , [ param_value [ ,…n ] ] )

Msg_number: The is the Id column value of sys.,messages. This function will return Null if msg_number  is less than 13000 or if the message doesn’t exist.

Msg_string : Now in SQL Server 2016 it enables you to pass your own message construct. You can now pass a string as well.

param_value:
It hold parameter value for use in the message. It Can be more than one parameter value. The value must be specified in the order in which place holders are placed.

The maximum length of msg_string is 2047 characters. If the message contains more than 2047 charcaters, then It will display only first 2044 characters and will add an ellipsis. Maximum 20 parameters can be passed to it.

It looks up the message in the current language of the user, if it doesn’t find any localized version of the message then it will use US English version.

In your message string the place holder should match with the type of value you wanted to show otherwise it will throw an error.

Example :

To show a string and an integer value, we need to use correct placeholder as per the data type.

Declare @v1 varchar(50) ='ABC', @v2 int =123
SELECT FORMATMESSAGE('String value  %s. ', @v1) AS message
SELECT FORMATMESSAGE('Integer value  %i. ', @v2) AS message

Formatmessage1

The variable you pass if doesn’t supported by the placeholder then it will throw an error.

Formatmessage2

FormatMessage function useful to write your own customize message.

Avatar

About Sandip Pani

Sandip Pani is a Database/BI developer and speaker and is located in Bangalore, India. He has been working with SQL Server over 11 years. He is MCITP for SQL Server 2008 and specializes in SQL Server for application developers and performance and query tuning. His specialization is into Query Tuning and performance troubleshooting. He is working as Senior Technical Specilist for NextGen Healthcare. He is active in SQL community world. He share and enhance his knowledge of SQL Server by spending time at newsgroups and forums, reading and writing blogs, and attending and speaking at conferences.

View all posts by Sandip Pani →

Leave a Reply

Your email address will not be published. Required fields are marked *