MySQL – Writing dynamic SQL in stored procedures

[adsense id=”0514458240″ width=”468″ height=”60″]

The following is sample code which shows how dynamic SQL can be generated within a stored procedure. In the example we are simply generating a stored procedure which takes a single parameter, a table name. We dynamically access the information of that table and return the results.

For dynamic SQL we need to generate the SQL, then prepare a statement for execution (PREPARE), execute (EXECUTE) the statement and then cleanup (DEALLOCATE PREPARE).

It should be noted that the example below gives much better performance by issuing the SQL directly rather than dynamically generating the SQL for execution. Below we have a table of 10 runs for both stored procedure versus direct SQL and it does show a difference. While a single run will not show any performance differences, if this is code contained within a processing LOOP each iteration gives the 1ms difference.

DROP PROCEDURE IF EXISTS aProc;
DELIMITER $$
CREATE PROCEDURE aProc(tableName VARCHAR(30))
BEGIN
	SET @s = CONCAT('SELECT * FROM ', tableName);
	PREPARE stmt FROM @s;
	EXECUTE stmt;
	DEALLOCATE PREPARE stmt;
END
$$
DELIMITER ;
	

Here are the results of running the stored procedure and the SQL natively.

Average time for stored procedure method 0.0267 seconds. Average time for native SQL 0.0256 seconds.