I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 218, comments - 2281, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

spSPrintF - an alternative to xp_sprintf

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!

Print | posted on Friday, May 05, 2006 2:34 PM | Filed Under [ SQL Server ]

Feedback

Gravatar

# re: spSPrintF - an alternative to xp_sprintf

But, %s is not the only possible parameter. Can you make it support %d, %n, %i, etc?
5/5/2006 4:24 PM | Adam Machanic
Gravatar

# re: spSPrintF - an alternative to xp_sprintf

sure.
when i have some more time. :))
5/5/2006 4:26 PM | Mladen
Gravatar

# re: spSPrintF - an alternative to xp_sprintf

Although... you have to put parameters into a string anyways... so what good would %d, %i, etc... do?
5/5/2006 5:05 PM | Mladen
Gravatar

# re: spSPrintF - an alternative to xp_sprintf

To be honest, I've never used xp_sprintf before, and I just assumed it supported all of the same tags that RAISERROR does (all of the C/C++ string format tags) -- I just looked in BOL, and only %s is supported. So I guess there would be absolutely no benefit!
5/7/2006 4:04 AM | Adam Machanic
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET