radha krishna radha krishna - 18 days ago 6
MySQL Question

MySQL:Viewing Dynamic Query in SQL format

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;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'SUM(CASE WHEN Status_Date = ''',
Status_Date,
''' then count_as_of_date else 0 end) AS `', Status_Date, '`' )
ORDER BY Status_Date ) INTO @sql
FROM finance_dashboard.dashboard_data;


Now, I would like to see how this query gets transformed into actual SQL query.

Is there any tool provided by MYSQL workbench, which can allow me to view the dynamic query into natural SQL statement?

Thanks

Answer

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 PREPARE and EXEC commands. Something like:

PREPARE Stat FROM 'SELECT * FROM ' + <tablename> + ' WHERE....' ;

and then:

EXECUTE Stat ;

Note: You can also use bindings for parameters.

Check this for details on how to use this type of functionality.