Table as a variable

Hi,
I think there is problrm with the query it should be

declare @query varchar(2000)

sert @query='select * from ' +@@table_name

exec (@query)

This should work



"Timothy Peer" wrote:

> I am reading a list of tables from a table and wish to execute a SQL
> statement for each table from this list. I tried declaring @table_name,
> depositing the table name from my table-list data. However, the "Select *
> from @table_name" SQL fails. Any ideas?
 >> Stay informed about: Table as a variable 

"Asit Kaushik" wrote in message


> "Timothy Peer" wrote:
>
>> I am reading a list of tables from a table and wish to execute a SQL
>> statement for each table from this list. I tried declaring @table_name,
>> depositing the table name from my table-list data. However, the "Select *
>> from @table_name" SQL fails. Any ideas?

> Hi,
> I think there is problrm with the query it should be
>
> declare @query varchar(2000)
>
> sert @query='select * from ' +@@table_name
>
> exec (@query)
>
> This should work


This, also, will not work. You cannot use a variable in the FROM clause of a
query under any conditions.

The above concept is valid; however, the execution is incorrect.

Given a preexisting variable @table_name containing a valid two-part table
name.

DECLARE @query varchar(max)
SET @query = 'SELECT * FROM ' + @table_name
EXEC (@query)

You could also skip the @query variable and simply:

EXEC('SELECT * FROM ' + @table_name)


--
Lawrence Garvin, M.S., MCITP(x2), MCTS(x5), MCP(x7), MCBMSP
Principal/CTO, Onsite Technology Solutions, Houston, Texas
 >> Stay informed about: Table as a variable 

On Dec 9, 11:46 am, "Lawrence Garvin" wrote:
> "Asit Kaushik" wrote in messagenews:21E460DB-C0FF-4B33-8514-536487ABCCF2@microsoft.com...
>
>
>
>
>
> > "Timothy Peer" wrote:
>
> >> I am reading a list of tables from a table and wish to execute a SQL
> >> statement for each table from this list. I tried declaring @table_name,
> >> depositing the table name from my table-list data. However, the "Select *
> >> from @table_name" SQL fails. Any ideas?
> > Hi,
> > I think there is problrm with the query it should be
>
> > declare @query varchar(2000)
>
> > sert @query='select * from ' +@@table_name
>
> > exec (@query)
>
> > This should work
>
> This, also, will not work. You cannot use a variable in the FROM clause of a
> query under any conditions.
>
> The above concept is valid; however, the execution is incorrect.
>
> Given a preexisting variable @table_name containing a valid two-part table
> name.
>
> DECLARE @query varchar(max)
> SET @query = 'SELECT * FROM ' + @table_name
> EXEC (@query)
>
> You could also skip the @query variable and simply:
>
> EXEC('SELECT * FROM ' + @table_name)
>
> --
> Lawrence Garvin, M.S., MCITP(x2), MCTS(x5), MCP(x7), MCBMSP
> Principal/CTO, Onsite Technology Solutions, Houston, Texas- Hide quoted text -
>
> - Show quoted text -

what about a cursor?
 >> Stay informed about: Table as a variable 

wrote in message


>> This, also, will not work. You cannot use a variable in the FROM clause
>> of a
>> query under any conditions.
>>
>> The above concept is valid; however, the execution is incorrect.
>>
>> Given a preexisting variable @table_name containing a valid two-part
>> table
>> name.
>>
>> DECLARE @query varchar(max)
>> SET @query = 'SELECT * FROM ' + @table_name
>> EXEC (@query)
>>
>> You could also skip the @query variable and simply:
>>
>> EXEC('SELECT * FROM ' + @table_name)


> what about a cursor?


What about it?


--
Lawrence Garvin, M.S., MCITP(x2), MCTS(x5), MCP(x7), MCBMSP
Principal/CTO, Onsite Technology Solutions, Houston, Texas
 >> Stay informed about: Table as a variable