Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


use a variable to identify table in FROM statement


use a variable to identify table in FROM statement

Author
Message
pcasey
pcasey
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 113
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
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8590 Visits: 18174
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
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14992 Visits: 39024
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
Andre Guerreiro
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1065 Visits: 1514
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
RBarryYoung
SSCrazy Eights
SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)SSCrazy Eights (9.5K reputation)

Group: General Forum Members
Points: 9456 Visits: 9517
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.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
pcasey
pcasey
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 113
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
pcasey
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 113
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
pcasey
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 113
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
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36391 Visits: 18763
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
pcasey
pcasey
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 113
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search