The name of the dynamic table of the SQL query in FOR

Question

I have a tbl1 table that has a tbl_names column. 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 request above will not work, but how can I achieve this? Do I need to write a stored procedure or something like that and quote on each value of the second request and execute the first request?

thanks

Answer 1

You can use the prepared instructions.

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

PREPARE Syntax

Greetings.

Answer 2

You will need to use dynamic SQL. Create an SQL string with the query 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 

Answer 3

Just add an alias to the subquery:

 select * from (select tbl_names from tbl1) a; 

Good luck)