sprintf in Transact SQL

Background

One of my first programming languages is C; mostly on IBM OS/2.

Transact SQL

Because of that puppy love, I am always tempted to try use some of the old functions such as sprintf in Transact SQL.

To me it is just a bit elegant to try working it in whenever I need to stitch a couple of data into one string. i.e. string merging.

We all know that SQL Server does not automatically handle string conversion.

Code


declare @dateToday datetime
declare @strToday varchar(60)
declare @iDay int
declare @iMonth int
declare @strMonth varchar(30)

set @dateToday = getdate()
set @iDay = datepart(day, @dateToday)
set @iMonth = datepart(month, @dateToday)
set @strMonth = datename(mm, @iMonth)


set @strToday = 'It is ' + @strMonth + @iDay


print @strToday



But, we get an error message that reads:


Msg 245, Level 16, State 1, Line 13
Conversion failed when converting the varchar value 'It is January' to data type int.

We know that the problem is because we are trying to concatenate strings and numbers without first converting them to be the same datatype.

String Concatenation – Matched data types

Get Today’s date, convert data that is not string to string, and print out the Month and Day



declare @dateToday datetime
declare @strToday varchar(60)
declare @iDay int
declare @iMonth int
declare @strMonth varchar(30)

set @dateToday = getdate()
set @iDay = datepart(day, @dateToday)
set @iMonth = datepart(month, @dateToday)
set @strMonth = datename(mm, @iMonth)


set @strToday = 'It is ' + @strMonth + @iDay


print @strToday


And, we are good:

It is January 10

String Concatenation – xp_sprintf

So if you are like me, and you don’t really like having little conversion code all over your Stored Procedure. And, so you try to abstract it out a bit.


declare @dateToday datetime
declare @strToday varchar(60)
declare @iDay int
declare @strDay varchar(10)
declare @iMonth int
declare @strMonth varchar(30)
declare @STR_TODAY_FORMAT varchar(80)

set @dateToday = getdate()
set @iDay = datepart(day, @dateToday)
set @iMonth = datepart(month, @dateToday)
set @strMonth = datename(mm, @iMonth)


/*
    set @strToday = 'It is ' + @strMonth + ' ' + cast(@iDay as varchar)
    print @strToday
*/

set @STR_TODAY_FORMAT = 'It is %s %i'

exec master.dbo.xp_sprintf @strToday output, @STR_TODAY_FORMAT, @strMonth, @iDay

But, we get an error message stating:

Error executing extended stored procedure: Invalid Parameter Type
Msg 50003, Level 1, State 0

And, you wonder why you are getting an error message stating “Invalid Parameter Type“:

So you read the manual at http://technet.microsoft.com/en-us/library/aa260704(v=sql.80).aspx

and, it states quite frankly:

Currently, only the %s format argument is supported

And, so you replace %i with %s:

But, we get the same error message.

Solution

The only that works is to pass in only strings, as in:

Code Snippet:

set @STR_TODAY_FORMAT = 'It is %s %s'
set @strDay = cast(@iDay as varchar)
exec master.dbo.xp_sprintf @strToday output, @STR_TODAY_FORMAT, @strMonth, @strDay
print @strToday

Full Code:


declare @dateToday datetime
declare @strToday varchar(60)
declare @iDay int
declare @strDay varchar(10)
declare @iMonth int
declare @strMonth varchar(30)
declare @STR_TODAY_FORMAT varchar(80)

set @dateToday = getdate()
set @iDay = datepart(day, @dateToday)
set @iMonth = datepart(month, @dateToday)
set @strMonth = datename(mm, @iMonth)


set @STR_TODAY_FORMAT = 'It is %s %s'
set @strDay = cast(@iDay as varchar)
exec master.dbo.xp_sprintf @strToday output, @STR_TODAY_FORMAT, @strMonth, @strDay
print @strToday

Another problem

Another problem with xp_sprintf is that your output string is limited to 255 characters.  It is really C after-all; with all the warts of extended Stored Procedures.

BTW, I really liked Open Data Services (ODS) — http://technet.microsoft.com/en-us/library/aa224743(v=sql.80).aspx.

Summary

I do wish that coding is like Paul Simon’s songs; where Mother and Child Reunions are only moments away.  As if they were, I will re reunited with sprintf and sccanf upon discovering them in Transact SQL.

For now familiarizing with The Drums’s “I hope Time Doesn’t change him” –  https://www.youtube.com/watch?v=foTt_n3zI3s.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s