Maybe someone here can help me out with this - I need to form a table name from some text combined with a parameter.

I figure there has to be a form of concatenation that would allow this, but it is beyond my knowledge and my quick google attempts have left me with nothing useful (can not used stored procedures for this - which is mostly all I found)

here is a chopped up/modified scenario to help explain:

declare @IDNum as varchar(128)
set @IDNum = (select min(id) from DefaultsTable)

case when (select count(idnum) from IDtable) = 0 then (select count(idnum) from zIDTable_ + @IDNum) else (select count(idnum) from IDtable) end as TotalIdCount,

the section in bold (hope it is in bold when I hit post) is the section in question

what I am trying to do is determine if the first select returns 0.. if it does I want to pull from a backup table which uses the same name as the initial table I check.. with the exceptions of including a Z in front of the name and a 6 digit number appended to the end of it. That 6 digit number I can pull from another defaults table, throw it in a parameter, and the only thing left for me to do is create the table name from that param.

I have tried any and all forms of logic I know to concatenate two strings.. +, &, ||.. nothing works.. throwing it in brackets.. piecing it as strings between ' '. I have nothing more to throw at it without picking someone else's brain who is far better with SQL than I am.

So.. any ideas/suggestions?

4/3/2006 10:48:29 AM

an update.. I decided to take the approach of dumping the select statement into a string and piecing it together into one parameter as follows:


declare @sql

set @sql = 'select count(idnum) from zIDTable_' + @IDNum


this works just fine to create the select statement - and I can attempt to use @sql.. but I need to use the EXEC statement which does not seem to jive well being within a CASE statement.. so once again I am scratching my head

4/3/2006 11:19:09 AM

Have you tried to just using "CONCAT()"

I would think it would work in MySQL that way, but I can't test it at the moment.

4/3/2006 11:19:59 AM

yep I've tried to no avail so far

I'm glad you said MySQL.. forgot to include in the first post... I'm using Microsoft SQL2000 here.

4/3/2006 11:26:37 AM

only way you'll be able to dynamically specify the table name is to use dynamic sql, which slows down your statements considerably (no caching, etc)


your own example using a variable string and using EXEC() is the only way to do it. instead of using a case statement, you can do this:


declare int @check
select @check = count(idnum) from IDtable

if @check = 0
begin

(whatever your statements are for true)

end
else
begin

(else?)

end


the end. happy coding

4/3/2006 1:47:31 PM

muckin around with something similar to that right now - I appreciate the input.. took me forever to realize to try it like that - it does work as intended.. my only problem is making it work with the other 10 aggregate selections within the same clusterfuck of a query right now

4/3/2006 1:59:08 PM

fun stuff... thank god for stored procedures

4/3/2006 2:01:22 PM

whoever setup the table structure should be shot.

4/3/2006 2:06:08 PM

^ .......

you have no idea how much it pissed me off to come in to work last Friday only to see so much shit break because someone let this schema change slip through to our latest build... I am supposed to be moving into business analysis here.. not fucking repairing all my SQL and code because someone decided the backup tables needed ID's appended to the end of them now

anyway.. enough griping.. I got it to work.. kinda.. this has gotta be the most fucked up query ever now.. lost every bit of effeciency I originally had

[Edited on April 3, 2006 at 2:35 PM. Reason : I know better.. haha]

4/3/2006 2:29:19 PM

MAKE SURE YOU'RE CASTING YOUR QUERY INTO THE CORRECT DATA TYPE SO YOU DON'T BREAK THE CHECKSUM OPERATIONS

4/3/2006 2:38:55 PM

dude breezer,

you don't even know about fucked up queries. I wish I could post the query I wrote for a "missing pages" report. My lord it was disasterous but that was only because of a horrible design spec for the report.

4/3/2006 2:41:35 PM

i'm a big fan of the 50-line stored procedure.

4/3/2006 2:50:19 PM

yeah this is why I struck out the comment - I've seen some seriously messed up queries before..

4/3/2006 2:59:14 PM

stored procedures are your friends

... its just weird writing code and having to deal with words such as "Validation" and "Auditability" and "Vioxx"

such is the world of the FDA.

4/3/2006 3:09:36 PM