Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2006

    Unanswered: Using Variable as table name in SELECT statement

    Good day all,
    trying to use variables to hold two pieces of information.
    The first being the SQL server name, and the second which is a table name, pulled from another table via a loop.
    these two variables are then strung together and held in a third variable which is then used in a select query.
    Tested the variables and it prints the correct string and loops perfectly, however the SELECt part does not like calling
    the table from a variable. Hard-coded it all works well but using the variable it falls over.

    I'm hoping my syntax is wrong so it can be corrected but if not then my method of using a variable for a table name in the SELECT statement is flawed and requires a re-think.
    The code is as follows, can anyone see why the SELECt statement does not like the variable?

    DECLARE @dbName varchar(50)
    DECLARE @tblName varchar(50)
    DECLARE @DBTbl varchar(50)
    set @dbName = 'DatabaseName.DBO.'
    set @tblName = ''

    While @tblName is not null
    select @tblName = min(MyTable)
    from tblTableNames
    WHERE MyField > @tblName
    set @DBTbl = @dbName + @tblName
    print @DBTbl

    This part works fine, if I add END, GO, it prints the list of tablenames in full.
    This point here it falls over...

    if @tblName is not null
    Insert INTO dbo.tblKeepCount
    initCount, wDate

    SELECT COUNT(Accounts) AS initCount, convert(varchar, getdate(), 101) AS wDate
    from @DBTbl

    The error message I get is:-

    Must declare the variable '@DBTbl'.

    So is it not possible to pull a table from a variable?

    Thanks in advance,

  2. #2
    Join Date
    Jun 2003
    Toronto, Canada
    Provided Answers: 1
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Sep 2006
    Thank you.

    By using dynamic SQL I was able to use the variable as a tablename.


  4. #4
    Join Date
    Apr 2002
    Toronto, Canada
    may i ask why you have a table of table names?

    something isn't right

    are you by any chance embedding a date or something into the table names? | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts