use a variable to identify table in FROM statement

Question

Post reply

use a variable to identify table in FROM statement

pcasey

SSC Enthusiast

Points: 112
More actions
August 24, 2012 at 11:11 am

#260993

Dozens of our tables are set up where the table names are identical except for an ID number at the end. I am often called upon to write queries for other employees to use, and I would like to simplify things for them by putting that ID number in a variable at the top of the query. Then, the user would only need to set the variable and click Execute.
Suppose, for example, a typical table name is called ClientTableCLT123. I have tried something like this:
DECLARE @ClientID varchar(6)
SET @ClientID='CLT123'
DECLARE @tablename varchar(20)
SET @tablename='ClientTable'+@ClientID
Select *
FROM Databasename.dbo.@tablename
It doesn't work. Apparently, my code isn't sufficient for identifying the table. I've tried some other combinations, but I suspect I'm way off somehow.
Please help.
Penny

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

You can't do that directly.

You could do it with dynamic code, but you need to be careful of SQL injection.

The best way to achieve it is to redesign you database (I'm sure it's not the easiest or the most likely).

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Lowell SSC Guru Points: 323518 More actions · Answer 2

Are all the tables the same layout/columns?

if they were, you could create One View to Rule Them All:

CREATE VIEW OneViewToRuleThemAll

AS

SELECT 'CLT123' AS ID, * FROM ClientTableCLT123 UNION ALL

SELECT 'CLT124' AS ID, * FROM ClientTableCLT124 UNION ALL

SELECT 'CLT125' AS ID, * FROM ClientTableCLT125

then your code would simply be like this:

DECLARE @ClientID varchar(6) ='CLT123'

SELECT * FROM OneViewToRuleThemAll WHERE ID = @ClientID

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 3

Or you could use a stored procedure that would receive the employer's id as a parameter and then have the code to check that and return data from the correct table. That way, you'd have only one stored procedure querying multiple tables. Unless you need to protect data from other employees that could work.

Dynamic queries (in that particular case) are too risky IMHO.

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

pcasey (8/24/2012)
Dozens of our tables are set up where the table names are identical except for an ID number at the end. I am often called upon to write queries for other employees to use, and I would like to simplify things for them by putting that ID number in a variable at the top of the query. Then, the user would only need to set the variable and click Execute.
...

This is a very non-Relational design and you are going to have no end of trouble with it. Although the previous replies contain many good suggestions, it needs to be said: The best possible solution would be to fix the design so that most of these tables with identical design are in fact the same table. Then you can add some kind of tenant/owner column to keep the rows separate, when you need to, but other than that, just use the same queries.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

pcasey SSC Enthusiast Points: 112 More actions · Answer 5

Thanks, everyone, for your assistance.

This is Monday morning, and it will take me a while to tinker with your suggestions and see if they work for me.

I completely agree that the database is a poor design; however, it's been long established at our company, and I have no power or authority to change it.

I'm a Data Analyst and not a DBA. I've got to work with what I have available.

Penny

pcasey SSC Enthusiast Points: 112 More actions · Answer 6

Thanks, Lowell -

Your Ring Thing makes sense up to a point. The layouts of all of the tables are identical, but the number of tables and the number of records in each table seems to me to be too large to be a good idea resource-wise.

I'm really hoping for a cookie-cutter solution where the user just sets the variable that he/she needs and runs the query for that table's particular results.

I have read about using a stored procedure, but I confess that I haven't actually understood how to do this.

Penny

pcasey SSC Enthusiast Points: 112 More actions · Answer 7

Andre -

I apologize for being dull-witted, but I need a little more help on the stored procedure since I have run them and read about them but never written one.

thanks,

Penny

Steve Jones - SSC Editor SSC Guru Points: 736458 More actions · Answer 8

Stored procedures are just sets of code, like a batch you'd run from SSMS.

you add a header with parameters like this:

create procedure MyProc

@myvar varchar(20)

as

Then you add the code you need. You could do something like this (header repeated:

create procedure MyProc

@myvar varchar(20)

as

if @myvar = 'CLI123' then

select * from CLI123

else if @myvar = 'CLI124'

select * from CLI124

Note, I don't like this idea. I think you're better to have separate code for separate tables, or use the view solution above.

pcasey SSC Enthusiast Points: 112 More actions · Answer 9

Terrific!

I can see, now, that it may be better just to query the individual tables as needed. Still, this gives me some options.

Thanks for keeping it simple.

Penny