Jonathan Malek

A while ago, I had written some code to log failed dynamic sql to a log table, for tracing and debugging purposes.  The code looks something like this:

exec sp_executesql @sql, …
if( @@error != 0 )
begin
   exec util_log_error ‘the name of the current stored proc’, 
         ‘Failed to perform some action: %s. 
          More data is available here: %s and here: %s, %s…’, 
          @sql, @moredata, @andmore, @etc, @etc
   return 1
end

The stored procedure “util_log_error” takes care of logging the message to the table, accepting up to 10 variables.  It wrapped master.dbo.xp_sprintf.  I finally found out why certain messages were being truncated–xp_sprintf has a maximum argument size of 255 characters (Found that in the BOL, did you?  No.  Not documented anywhere I could find.  Found it in Google groups)  Some of the dynamic sql we produce gets much larger than that, and it was being truncated.

So after googling unsuccessfully for a while, I decided before I wrote my own COM object to do this trivial task, and called it through the sp_OA procs, that I would look at some of the string functions to see if I could do the same in T-SQL.  In a moment of BOL clarity, I found a way.

It all starts with the STUFF function, a little gem I’ve never used and too often thought was useless.  A little proof of concept is in order:

declare @message varchar(1000),
   @arg1 varchar(400),
   @arg2 varchar(400),
   @repeat int,
   @count int,
   @local varchar(400)

select @message = ‘The first part: %s and the second part: %s’,
   @arg1 = replicate( ‘a’, 400 ),
   @arg2 = replicate( ‘b’, 400 ),
   @repeat = charindex( ‘%s’, @message ),
   @count = 0

while( @repeat > 0 )
begin
   select @count = @count + 1, @local = CASE @count
       when 1 then @arg1
       when 2 then @arg2
       end,
       @message = stuff( @message, @repeat, 2, @local ),
       @repeat = charindex( ‘%s’, @message )
end

print @message

Just keep adding when cases for each argument you might have.  And this could be made better.  Right now, xp_sprintf only supports ‘%s’ replacement.  I leave ‘%d’ support as an exercise to the reader–suffice it to say that I believe it starts with sql_variant, has SQL_VARIANT_PROPERTY( @arg, ‘BaseType’ ) in the middle, and ends with cast().  Say with me, “goodbye xp_sprintf!”