Must declare the table variable "@tableName".

• Dunger

  Old Hand

  Points: 302

  More actions

  November 30, 2007 at 5:13 am

  #201368

  Hi,

  Hope someone can offer me some guidance here.

  When I try to execute the following tSQL, I get the following error -

  Msg 1087, Level 15, State 2, Line 16

  Must declare the table variable "@tableName".

  ==========================

  Code

  CREATE VIEW swpro.queuesV

  AS

  SELECT DISTINCT SUBSTRING (swpro.queue_phys_descr, 6, 100) AS queueName

  FROM swpro.iql_queues

  GO

  DECLARE @tableNameVARCHAR(100)

  -- Create CURSOR and iterate through this list getting required info.

  DECLARE cDetail CURSOR FAST_FORWARD READ_ONLY

  FOR SELECT queueName

  FROM swpro.queuesV

  OPEN cDetail

  FETCH cDetail INTO @tableName

  WHILE @@Fetch_Status = 0

  BEGIN

  PRINT @tableName

  SELECT * FROM @tableName

  FETCH cDetail INTO @tableName

  END

  CLOSE cDetail

  DEALLOCATE cDetail

  DROP VIEW swpro.queuesV

  ==========================

  I get the feeling I have to declare @tableName as a type TABLE, but is this really required for what I am trying to achieve which is basically use a column value as the table name I want to select from?

  *Please Note *

  The code I provide here might not be the most efficient or effective to get the result I am after and any suggestions would be appeciated. Still learning tSQL! 🙂

  Many Thanks in advance for any answers supplied,

  Cheers,

  Scott

• Andras Belokosztolszki

  SSC-Insane

  Points: 21971

  More actions

  November 30, 2007 at 5:23 am

  #755897

  You could use dynamic SQL like:

  declare @q varchar(1000)

  set @q = 'SELECT * FROM ' + @tableName

  exec (@q)

  Regards,

  Andras

  ---

  Andras Belokosztolszki, MCPD, PhD
  GoldenGate Software

• Grant Fritchey

  SSC Guru

  Points: 398679

  More actions

  November 30, 2007 at 5:55 am

  #755913

  In order to select FROM something, it needs to be a table valued object (table, view, function, temp table, table variable, OPENXML, XQuery). You can run a query against a variable like this:

  SELECT @Variable

  That should work.

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning

• Dunger

  Old Hand

  Points: 302

  More actions

  November 30, 2007 at 6:55 am

  #755934

  Thanks!

  Using the Dynamic SQL approach has worked a treat. 🙂

  Cheers,

  Scott