Post reply

table name string to table variable

  • March 25, 2004 at 5:27 pm

    #152088

    Thats the best subject I could come up with

    If I have the name of a table as a varchar, how can I get a "reference" to the table so that I don't have to use dynamic sql in my proc's?

    I have a system where I am storing denormalized archive information in a series of tables. The tables have a naming convention made up of the object name being denormalized and a version number. When the table is created I add a row to an "archiveManager" table where one of the fields is the foreign key of the still normailzed object and another field contains the name of the archive table.

    What I am doing now is querying the archivemanger using the key to get the archive table name, then I amusing that name to construct my statment as dynamic sql.

    select @tblName = (select tablename from archivemanager where course_fk=2)

    set @dynsql = "select this and that from ' + @tblname +' where this and that'

    exec @dynsql

    I am not crazy about using dynamic sql and was wondering if there was a way to get a "reference" to the table based on its name.

    thanks

    Dave

     

     

     

     

     

     

    Dave

    Trainmark.com IT Training B2B Marketplace
    (Jobs for IT Instructors)

  • March 25, 2004 at 5:50 pm

    #500531

    No not directly using a variable to a select statement. You could do in an SP an IF for each table and run the right query against the right table but that can require a lot of work a or a little based on the number of tables and alternate conditions. ANd if you do go that route there are tricks you casn use to get best performance and issues you need to keep in mind when doing.

  • March 25, 2004 at 6:13 pm

    #500538

    Thanks antares, these archive tables are created on the fly by client activity so there's no way for me to branch in a proc.

    I think I have decided to correct a bad behavior instead. If these are denormalized archive tables why the heck am I trying to access them as transactional tables!

    Instead, I'll maintain two roll up tables, one normalized one that I'll use in the app, the other will remain a denormalized archive, simply used for clients to download as CSV etc.

    Cheers

    dave

    Dave

    Trainmark.com IT Training B2B Marketplace
    (Jobs for IT Instructors)

  • March 25, 2004 at 6:15 pm

    #500540

    Good luck and does sound like a far better choice.

Viewing 4 posts - 1 through 3 (of 3 total)

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