November 30, 2007 at 5:13 am
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
November 30, 2007 at 5:23 am
You could use dynamic SQL like:
declare @q varchar(1000)
set @q = 'SELECT * FROM ' + @tableName
exec (@q)
Regards,
Andras
November 30, 2007 at 5:55 am
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
November 30, 2007 at 6:55 am
Thanks!
Using the Dynamic SQL approach has worked a treat.
Cheers,
Scott
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy