I have searched about this a lot over the Internet, but could not find any mechanism.
My question is with regards to MySQL dynamic query. I have a query like this:
SET @sql = NULL;
'SUM(CASE WHEN Status_Date = ''',
''' then count_as_of_date else 0 end) AS `', Status_Date, '`' )
ORDER BY Status_Date ) INTO @sql
What you posted is not a dynamic query (and actually, it looks you have some syntax errors as well).
A dynamic query (normally referred to as DYNAMIC SQL) is used to dynamically phrase a query into a string, and then tell the DBMS to execute the query contained in that string.
For instance, suppose that you need to retrieve data from either TABLE1 or TABLE2 depending on some condition. You could either use and
IF ... THEN ... ELSE... construct or build a string using the applicable table name and then use
EXEC commands. Something like:
PREPARE Stat FROM 'SELECT * FROM ' + <tablename> + ' WHERE....' ;
EXECUTE Stat ;
Note: You can also use bindings for parameters.
Check this for details on how to use this type of functionality.