How to Format a system message or a localized message.

Problem: How to make a contextual meaning to a system error.

Solution: FORMATMESSAGE

Starting from SQL Server 2012 and above Format message creates place holders when sending a custom message during an exceptional handling.

There can be up to 20 place holders to send via Format message.

Place holders are similar to variables that hold a value, this keep place inside the message to print the context of the message.

%s- is used as a string placeholder.

%d-is used as an integer placeholder.

Example:

In this article I am going to show you we can send meaning full contextual message.

Example 1:

In this example lets create a system message and use string place holders to pass the table name and column name. Then use Throw to send the message by passing arguments to the FORMATMESSAGE function.

--adding custom message to use the message id number in THROW.

EXECUTE sp_addmessage 50050,16,  N'The table %s doesnt allow duplicates on column %s';

GO

BEGIN TRY 

 INSERT INTO dbo.TableZ(Id,Col1) 

 SELECT 1,'a' 

 UNION ALL

 SELECT 1,'b'

END TRY

BEGIN CATCH

 Declare @msg VARCHAR(200) =FORMATMESSAGE(50050,'TableZ', 'Id');

 THROW 50050,@msg,1;

END CATCH

               

OUTPUT:

(0 row(s) affected)

Msg 50050, Level 16, State 1, Line 33

The table TableZ doesnt allow duplicates on column Id

Example 2: In this example, I would like to show how we can send a custom signature to all the viewers of my blog by passing a variable in to the formatmessage function, place holders.

DECLARE @Signature varchar(8000) =

        'Dear %s' + char(13) + char(13) +

        'Thank you for subscribing  Blog %s.' + char(13) + char(13) +

        'Yours Sincerly' + char(13) + 

                                '%s'

DECLARE @Message VARCHAR(max) =

        FORMATMESSAGE(@Signature, 'Alex','LearnSQLConcepts','Mahesh');

PRINT @Message;

OUTPUT:

Dear Alex

Thank you for subscribing  Blog LearnSQLConcepts.

Yours Sincerly

Mahesh

This can leverage the amount of time spent to create a custom signature or variable passed text message.