Running SQL Server 2000. I'm wondering if either of two techniques
incorporates a performance penalty or another hidden problem.
At issue is the ability to write code such as
Fine as far as it goes. Now, I've already boasted about devising
a sturdier format for this trick:
But it still has disadvantages, such as requiring multiple REPLACE
statements (which could be nested, sure) to pull it off, and basically
being a little clumsy.
Then I noticed the xp_sprintf extended stored procedure:
No multiple replaces, and uses a built-in function.
On the other hand, the parameters have to be (HAVE to be)
one of the char types (explicit convert-in-place /is/ acceptable),
whereas you can just REPLACE a string with an integer and
conversion is implicit. Also, if you want to use one
variable fif* times then it would appear that it has
to be a parameter fif* times over.
(Some sprintf implementations allow %... references to be
qualified to point to a numbered element in the parameter
set rather than taken in order, but apparently this implementation
doesn't do that, and also does only do %s officially, although
what happens if you actually put %x is intriguing...anyone know
what that is?)
Also, xp_sprintf presents the mild inconvenience that if you
want a character '%' in the output then you have to put '%%'
in the input - "...WHERE colname LIKE ''%%whatever%%''"
to end up executing "...WHERE colname LIKE '%whatever%'".
Well, I could do that; I'm doing it for quote marks, obviously.
So...is there another reason to prefer to avoid xp_sprintf ?
The name does imply that it leaves SQL Server proper and calls
the underlying operating system - but is that expensive in
processor time, or is it a simple, quick DLL call?
Is xp_sprintf - well - okay to use?