in xp_sprintf up to 50 arguments can be specified. sometimes there's a need for more.
This is it and it could as easily be rewritten as a function.
create proc spSPrintF
@body varchar(8000) = '',
@params varchar(1000) = '',
@paramSeparator varchar(10) = ','
as
begin
if @params = ''
begin
select @body
return
end
-- so we don't have to specially handle the last item
set @params = @params + @paramSeparator
declare @param varchar(200)
while @params <> ''
begin
select @param = replace(left(@params, charindex('"' + @paramSeparator, @params)), '"', ''),
@body = stuff(@body, charindex('%s', @body), 2, @param),
@params = replace(@params, '"' + @param + '"' + @paramSeparator, '')
end
select @body
end
go
declare @body varchar(8000), @params varchar(1000)
select @body = 'This is a %s text i have to %s with some %s i want',
-- Parameters must be in format '"parameterValue","parameterValue2","parameterValue3"'
-- no spaces between parameters!!!
@params = '"sample","replace","parameteres"'
exec spSPrintF @body, @params
The question that spawned this function was asked here. When Ryan Randall reminded me you can use Stuff function i naturally had to use it. :)) Thanx Ryan!