Sibtain Norain
Sibtain Norain

Reputation: 679

SQL query dynamic table name in FOR

I have a table tbl1 which has a column tbl_names. This column contains the name of some other tables.
Now I want to write a query in the following format:
select * from (select tbl_names from tbl1)

I know that the query above will not work but how I can achieve this? Do I need to write a stored procedure or something like that and loop on each value of second query and execute first query?

Thanks

Upvotes: 7

Views: 4840

Answers (3)

Carlos González
Carlos González

Reputation: 362

You can use prepared statements

SET @a = (select tbl_names from tbl1);
SET @x := CONCAT('SELECT * FROM ', @a);
Prepare stmt FROM @x;
Execute stmt;
DEALLOCATE PREPARE stmt;

PREPARE Syntax

Cheers.

Upvotes: 7

Sean
Sean

Reputation: 1474

You'll need to use some dynamic SQL. Build up a SQL string with the query that you want to execute and then call exec(@sql)

Full example:

declare cur cursor for
select tbl_names from tbl1

declare @sql varchar(100), @tbl varchar(100)

open cur

fetch cur into @tbl

while @@FETCH_STATUS = 0 begin
    set @sql = 'select * from ' + @tbl
    exec(@sql)
    fetch cur into @tbl
end

close cur
deallocate cur

Upvotes: 0

corvinusz
corvinusz

Reputation: 584

Just add a pseudonim to the subquery:

select * from (select tbl_names from tbl1) a;

Good luck )

Upvotes: -1

Related Questions