Using FormatMessage In SQL Server

Çağlar Özenç

3 years ago

In today’s article I will talk about Using Formatmessage in SQL Server.

FormatMessage has been around with SQLServer since version 2008. We use it to create a message from a defined message that exists in the sys.messages table. The FORMATMESSAGE function is the same as the RAISERROR statement.

Syntax:

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

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

The maximum length of msg_string is 2047 characters. If the message contains more than 2047 charcaters, then it will only display the first 2044 characters and add three points.

If the message does not find any local version of the user, it will use the US English version if it cannot find a localized version of the message.

Declare   @v1 varchar(50) = ‘MSHOWTO.ORG’ , @v2 int = 2019
SELECT   FORMATMESSAGE ( ‘Metinsel değer : %s  ‘ ,   @v1 )   AS   Mesaj
SELECT   FORMATMESSAGE ( ‘Sayısal değer  : %i ‘ ,   @v2 )   AS   Mesaj

Declare @v1 varchar(50) = ‘MSHOWTO.ORG’ , @v2 int = 2019

SELECT FORMATMESSAGE ( ‘Metinsel değer : %s ‘ , @v1 ) AS Mesaj

SELECT FORMATMESSAGE ( ‘Sayısal değer : %i ‘ , @v2 ) AS Mesaj

An error occurred if the variable you passed is not supported by the placeholder.

Declare @v1 varchar(50) = ‘MSHOWTO.ORG’
SELECT FORMATMESSAGE ( ‘Sayısal değer : %i ‘ , @v1 ) AS Mesaj

1 2	Declare @v1 varchar(50) = ‘MSHOWTO.ORG’ SELECT FORMATMESSAGE ( ‘Sayısal değer : %i ‘ , @v1 ) AS Mesaj

FormatMessage is useful for writing your own custom message.