use a variable to identify table in FROM statement

  • 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

  • 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
  • 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!

  • 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

  • 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]

  • 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

  • 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

  • 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

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

  • 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

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

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