Sql Server: Unusual String Functions

One of  unusual string functions in sql server is formatmessage()

What is formatmessage() function?
and where we may need to use this unusal function ?

Using FORMATMESSAGE(), you can format strings using a printf()-like syntax. It takes a parameter
specifying the ID of the message from the master..sysmessages table that you want to use, as well as a list of
arguments to insert into the message. FORMATMESSAGE() works similarly to RAISERROR(), except that it
doesn’t return an error. Instead, it returns the resulting message as a string, which you may then do with as
you please. Unfortunately, FORMATMESSAGE() is limited to messages that exist in sysmessages—you can’t
use it to format a plain character string.

With the help of formatmessage() function we can provide awesome results for printing our result query

Here’s a technique for working around that

First we need create table

and then need to run our query


CREATE TABLE #engagements
(Engagement varchar(30),
 EngagementStart smalldatetime,
 EngagementEnd smalldatetime)
INSERT #engagements VALUES('Gulf of Tonkin','19640802','19640804')
INSERT #engagements VALUES('Da Nang','19650301','19650331')
INSERT #engagements VALUES('Tet Offensive','19680131','19680930')
INSERT #engagements VALUES('Bombing of Cambodia','19690301','19700331')
INSERT #engagements VALUES('Invasion of Cambodia','19700401','19700430')
INSERT #engagements VALUES('Fall of Saigon','19750430','19750430')
DECLARE @msg varchar(60), @msgid int, @Engagement_id varchar(10), @inprint int
SELECT @msgid=ISNULL(MAX(error)+1,999999) FROM master..sysmessages WHERE error >50000
SELECT @Engagement_id=CAST(Engagement AS varchar), @inprint=COUNT(*) FROM #engagements GROUP BY Engagement
 --Get the last one
EXEC sp_addmessage @msgid,1,'Show  Format Message: %s has %d titles in print'
SET @msg=FORMATMESSAGE(@msgid,@Engagement_id,@inprint)
(No column name)
Show  Format Message: Tet Offens has 1 titles in print


and results was successful.
We got good result for printing from simple data

you can see this article in msdn wiki page too:


Sql Server: Unusual String Functions