Table names in dynamic SQL - choice of technique?

Table names in dynamic SQL - choice of technique?

Post by Robert Carneg » Wed, 16 Oct 2002 21:20:02



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?

 
 
 

Table names in dynamic SQL - choice of technique?

Post by Erland Sommarsko » Fri, 18 Oct 2002 07:41:25



> 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.

Clumsy, but performancewise probably ignoreable. It is rarely on those
kind of this you lose time on SQL Server. There is no disk access
involved.

I don't know about xp_sprintf, but I would be a little more nervous
about possible performance penalties here. Then again, they are
probably negligible.

A really weird thing to use would be formatmessage().

--

I support PASS - the definitive global community for SQL Server
professionals - http://www.sqlpass.org
The PASS Community Summit is in Seattle Nov 19-22, I'll be there. And you?

 
 
 

Table names in dynamic SQL - choice of technique?

Post by Robert Carneg » Fri, 18 Oct 2002 20:21:38




> > 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.

> Clumsy, but performancewise probably ignoreable. It is rarely on those
> kind of this you lose time on SQL Server. There is no disk access
> involved.

> I don't know about xp_sprintf, but I would be a little more nervous
> about possible performance penalties here. Then again, they are
> probably negligible.

> A really weird thing to use would be formatmessage().

Hey, yeah!

Advantages: it accepts most data types, not only char,
and has a wider range of sprintf options than xp_sprintf.
And the text of the "error message" can easily be retrieved
from the sysmessages table into any database context.

Disadvantage: limit of either 400 or 510 characters ;-)

Something else comes to mind, though: someone wanted a function
where you put a number in and it comes out as a string containing
the hexadecimal or other-format version of the number.  But it
seems there is no such function built in...except for
formatmessage()...??

It isn't documented to do binary, which we were also considering,
(%b doesn't work), but hex will do to be going on with.

And maybe you could stand to convert int to octal this way and then
replace the digits 0 through 7 with...hmm, (i) replace '0' with '000',
(ii) replace '1' with '001', (iii) replace '2' with '010' and '3'
with '011' and '4' with '100' and so forth.  (That the order of
the first two steps is critical is probably clear.)  Or, heck,
just learn to read octal.

Hmm...messages below 13001 are not available this way?  However,

exec sp_addmessage  -- into sysmessages (must be done this way)




print formatmessage(55556,1024)

Output: "Robert Carnegie wants the hex conversion of 0x400"

Funky!

 
 
 

Table names in dynamic SQL - choice of technique?

Post by Erland Sommarsko » Sat, 19 Oct 2002 07:35:26



> Something else comes to mind, though: someone wanted a function
> where you put a number in and it comes out as a string containing
> the hexadecimal or other-format version of the number.  But it
> seems there is no such function built in...

You can do this:

   select master.dbo.fn_varbintohexstr(convert(binary(2), 983))

Note however that fn_varbintohexstr is not a documented function, and
any use of it, is completely unsupported.

--

I support PASS - the definitive global community for SQL Server
professionals - http://www.sqlpass.org
The PASS Community Summit is in Seattle Nov 19-22, I'll be there. And you?