Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Must declare the table variable "@tableName". Expand / Collapse
Author
Message
Posted Friday, November 30, 2007 5:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 23, 2012 2:13 AM
Points: 2, Visits: 11
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 @tableName VARCHAR(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
Post #428026
Posted Friday, November 30, 2007 5:23 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 26, 2012 5:26 AM
Points: 1,367, Visits: 1,585
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
Post #428033
Posted Friday, November 30, 2007 5:55 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 8:48 AM
Points: 15,621, Visits: 28,005
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #428052
Posted Friday, November 30, 2007 6:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 23, 2012 2:13 AM
Points: 2, Visits: 11
Thanks!

Using the Dynamic SQL approach has worked a treat. :)

Cheers,
Scott
Post #428078
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse