I have tables like
changes201101
changes201102
changes201103
...
changes201201
And table
whichchanges
which contain rows Year and MONTH
How I can select * from changes from whichchanges
?
I type this query
SET @b := SELECT CONCAT('changes',year,month) FROM whichchanges;
((((@b should contain now multiple rows of changesYearMonth)))))
SET @x := SELECT * FROM @b;
Prepare stmt FROM @b;
Prepare stmt FROM @x;
Execute stmt;
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT CONCAT('changes',year,month) FROM changes)' at line 1
First, declare two variables, @table for holding the name of the table from which you want to query and @sql for holding the dynamic SQL. Second, set the value of the @table variable to production. products . Fourth, call the sp_executesql stored procedure by passing the @sql parameter.
Syntax for dynamic SQL is to make it string as below : 'SELECT statement'; To run a dynamic SQL statement, run the stored procedure sp_executesql as shown below : EXEC sp_executesql N'SELECT statement';
Solution 1. The only way to do that is use build your command into a string, and use EXEC to run the result: table and column name parsing is conducted early in the SQL command execution process and have been replaced before any of the actual query is executed.
You open 1 (
and close 2 )
. Remove the last:
SELECT CONCAT('changes',year,month) FROM changes
the second statement should probably be
SET @x := SELECT * FROM (@b) as b;
That works, but not sure if that is what you want:
SET @b := 'SELECT CONCAT(''changes'',`year`,`month`) FROM whichchanges';
SET @x := 'SELECT * FROM (SELECT CONCAT(''changes'',`year`,`month`) FROM whichchanges) as b';
Prepare stmt FROM @b;
Prepare stmt FROM @x;
Execute stmt;
If I understood you right you are looking for that single query:
select * from changes
where change_column in (select distinct concat(`year`, `month`) from whichchanges)
select @b := group_concat(concat(' select * from changes', `year`, `month`, ' union ') separator ' ') as w from whichchanges;
set @b := left(@b, length(@b) - 6);
Prepare stmt FROM @b;
Execute stmt;
SQLFiddle example
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With