bronze and silver award from Technet Guru Comptetion December 2015

Thanks for Microsoft for  bronze and silver  award from

Technet Guru Comptetition December 2015

december guru

and you can see this article in msdn wiki ninja blogs too:

http://blogs.technet.com/b/wikininjas/archive/2016/01/19/technet-guru-december-2015-winners.aspx

Реклама
bronze and silver award from Technet Guru Comptetion December 2015

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
BEGIN TRAN
EXEC sp_addmessage @msgid,1,'Show  Format Message: %s has %d titles in print'
SET @msg=FORMATMESSAGE(@msgid,@Engagement_id,@inprint)
ROLLBACK TRAN
SELECT @msg
(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:

http://social.technet.microsoft.com/wiki/contents/articles/33038.sql-server-unusual-string-functions.aspx

Sql Server: Unusual String Functions