There are dangers to using the xp_sprintf system call in SQL Server 2000.

• Firstly, it has a 256 character limit, and
• secondly, it suffers from a buffer overflow bug in some versions which can cause all sorts of unpredicatable damage.
• it also provides a vulnerability in that an attacker can inject code which could gain access to your data.

In short, it wasn’t one of Microsoft’s best moments.

There are a few occasions where something like the xp_sprintf is very handy. I find that, when writing a multi-lingual application where the user can set the language used at any point in time, it is very handy to have any text read from a table with placeholders for the actual data.

The english version of the string might be ‘You have %s%s%s left in your account’, with the first parameter being the prefix currency symbol (e.g. ‘£’) or blank if it is postfix, the second being the value, and the third being any postfix currency symbol.

I discovered the 256 -character limit of xp_sprintf by accident, late at night trying to meet a deadline whilst trying to work out why strings were getting truncated.

Here is a version, as a user function, returning a varchar(8000), that has a more reasonable 8000 character limit. Whilst I was about it, I made the delimiter for the insertion list configurable, and the placeholder (traditionally ‘%s’) configurable too

Having done it, I wondered whether this sort of routine could be developed to extend its usefulness. Could it be rewritten for TEXT variables, for example? could it be altered to add resultsets in the same way as sp_MakeWebTask, or to do more complex HTML presentation jobs?

I’d be interested to hear from anyone who has done so.