/* 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. */
ALTER function dbo.ufsSPrintf
(
@FormatString VARCHAR(8000),
@Args VARCHAR(8000), -- delimited list
@Delimiter VARCHAR(80)=',',
@PlaceHolder VARCHAR(80)='%s'
)
returns VARCHAR(8000) AS
BEGIN
DECLARE @Start INT
DECLARE @End INT
DECLARE @Where INT
DECLARE @LenDelimiter INT
DECLARE @LenPlaceholder INT
DECLARE @ii INT
SELECT
@Start=1,
@LenDelimiter=LEN(@Delimiter),
@LenPlaceholder=LEN(@placeholder),
@ii=1
IF (@formatString+@Args+@Delimiter+@placeholder IS NOT NULL)
WHILE(1=1)
BEGIN
SELECT @end=
CHARINDEX(@delimiter,COALESCE
(@Args,'')
+@delimiter,
@start),
@Where=CHARINDEX
(@PlaceHolder, @FormatString)
IF @End<@start+1 OR @Where=0 BREAK
--if no more variables or placeholders
SELECT @FormatString=
STUFF (@FormatString,
@where,
@lenPlaceholder,
SUBSTRING
(@Args,@start,@End-@Start))
SELECT @start=@end+@lenDelimiter,@
ii=@ii+1
END
RETURN REPLACE(@FormatString,@PlaceHolder,'')
END
/*
Usage:
select dbo.ufsSprintF ('Dear %s,
your account is now overdrawn to the value of £%s beyond your
credit limit of £%s and in consequence we must ask for an
immediate payment of £%s.
Assuring you of our continued service
Signed
%s, manager','Miss Cannon|25|100|125|Phil Factor','|','%s')
select dbo.ufsSprintF ('to %s, or not to %s. That is the question'
,'be|be'
,'|'
,'%s')
*/
SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS ON