radha krishna radha krishna - 1 year ago 106
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;
'SUM(CASE WHEN 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?


Answer Source

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:


Note: You can also use bindings for parameters.

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download