Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

use a variable to identify table in FROM statement Expand / Collapse
Author
Message
Posted Friday, August 24, 2012 11:11 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 22, 2013 8:00 AM
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

Post #1349823
Posted Friday, August 24, 2012 11:53 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 12:55 PM
Points: 4,046, Visits: 9,200
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1349850
Posted Friday, August 24, 2012 12:02 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:35 PM
Points: 12,962, Visits: 32,498
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1349852
Posted Sunday, August 26, 2012 12:58 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, December 15, 2014 3:02 AM
Points: 912, Visits: 1,500
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
Post #1350145
Posted Sunday, August 26, 2012 3:19 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, December 4, 2014 7:52 AM
Points: 9,294, Visits: 9,495
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."
Post #1350161
Posted Monday, August 27, 2012 8:28 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 22, 2013 8:00 AM
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
Post #1350421
Posted Monday, August 27, 2012 8:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 22, 2013 8:00 AM
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

Post #1350426
Posted Monday, August 27, 2012 8:38 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 22, 2013 8:00 AM
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
Post #1350432
Posted Monday, August 27, 2012 8:42 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 5:38 PM
Points: 31,368, Visits: 15,834
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
Post #1350435
Posted Monday, August 27, 2012 9:19 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 22, 2013 8:00 AM
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
Post #1350447
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse