Post reply

Variable as table name

  • November 18, 2005 at 4:46 am

    #188454

    Hi - I have a report (Crystal) that needs to use a table of a differing name each time.  The name of the table increase by one each time I.E. table_7, table_8, table_9 etc.

    I have written the following query to get the most current table to be used, but now need to know how to use the variable '@tmp2' as my table in a SELECT query I.E:  "SELECT * FROM @tmp2":

     

    declare @tmp2 as char

    set @tmp = (select MAX(crdate) from sysobjects where name LIKE 'table_%')

    select * from sysobjects where crdate = @tmp

    set @tmp2 = (select name from sysobjects where crdate = @tmp)

     

    Any help / advise would be greatly appreciated. - Thanks in advance.

  • November 18, 2005 at 6:16 am

    #604612

    You could do something like this...

    declare @tableName as varchar(100)
set @tableName = (select top 1 name from sysobjects where name like 'table_%'
order by crdate desc)
exec('select * from ' + @tableName)







    **ASCII stupid question, get a stupid ANSI !!!**

  • November 18, 2005 at 6:55 am

    #604621

    Absolutely brilliant! - Just what I was looking for, I cant thank you enough.

  • November 18, 2005 at 7:20 am

    #604628

    Glad to have helped!







    **ASCII stupid question, get a stupid ANSI !!!**

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

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