Re: Calling stored procedures ... Dynamic SQL
You CAN execute Dynamic SQL from a mysql stored procedure using prepared statements (from correspondence with MySQL this might not 'officially' be supported but it works with the release candidate).
Here's a couple ways to handle a stored procedure which uses an input parameter in a prepared statement.
-- Concatenate SQL statement and input parameters before PREPARE
DELIMITER \\
DROP PROCEDURE IF EXISTS `sp_test_dynamic_sql`\\
CREATE PROCEDURE `sp_test_dynamic_sql` (_min int, _max int)
BEGIN
SET @s = CONCAT('SELECT * FROM authors WHERE id BETWEEN ', _min, ' AND ', _max);
PREPARE stmt1 FROM @s;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END\\
DELIMITER ;
-- PREPAREd statement executed with input variables. You must assign the input variables to a User Variable (manual section 9.3), the PREPARE statement does not seem to work with input or other DECLAREd variables (at this time?).
DELIMITER \\
DROP PROCEDURE IF EXISTS `sp_test_dynamic_sql`\\
CREATE PROCEDURE `sp_test_dynamic_sql` (_min int, _max int)
BEGIN
SET @s = 'SELECT * FROM authors WHERE id BETWEEN ? AND ?';
SET @min = _min;
SET @max = _max;
PREPARE stmt1 FROM @s;
EXECUTE stmt1 USING @min, @max;
DEALLOCATE PREPARE stmt1;
END\\
DELIMITER ;
-- Here's some SQL to create an 'authors' table to test the above stored procedures.
CREATE TABLE authors
(
id int AUTO_INCREMENT PRIMARY KEY,
name varchar(20)
);
INSERT INTO authors(name) VALUES ('Rich');
INSERT INTO authors(name) VALUES ('Mary');
INSERT INTO authors(name) VALUES ('Jane');
INSERT INTO authors(name) VALUES ('Steve');
INSERT INTO authors(name) VALUES ('Norman');