How to use a variable in from of a query

Question

I need use a @var in from of a query

SET @table := (SELECT 
                  SCHEMA_NAME
           FROM
          information_schema.SCHEMATA
           WHERE
          SCHEMA_NAME LIKE 'test%'
           LIMIT 1);

SELECT 
   *
FROM 
   @table;

Hi, and welcome to Stack Overflow. As it stands, your question is hard to answer - but I _think_ you need dynamic SQL. https://stackoverflow.com/questions/190776/how-to-have-dynamic-sql-in-mysql-stored-procedure — Neville Kuyt, Apr 09 '19 at 14:39

score 0 · Accepted Answer · answered Apr 09 '19 at 14:41

You need to declare the table before you can set the table.

DECLARE @table TABLE ( SCHEMA_NAME NVARCHAR(MAX) )

INSERT INTO @table
SELECT SCHEMA_NAME
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME LIKE 'test%'

If you were trying to limit the table to 1 row with the "Limit 1", just use "TOP 1" like so:

INSERT INTO @table
SELECT TOP 1 SCHEMA_NAME
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME LIKE 'test%'

After this is set, you can query the temporary table:

SELECT * FROM @table

score 0 · Answer 2 · answered Apr 09 '19 at 15:30

You need to use dynamic SQL for this. In MySQL that is prepare/execute:

SELECT @table := SCHEMA_NAME
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME LIKE 'test%'
LIMIT 1;

SET @sql = CONCAT('SELECT * FROM ', @table);

prepare s from @sql;

execute s;

I should note that treating a schema like a table is really strange, but that is what you are asking about. If you want tables, you should be using information_schema.tables.

How to use a variable in from of a query

2 Answers2