Re: Execute an STRING as a query - Dynamic SQL -> PREPARE

[Rich Stefkovich]

You're trying to use mssql's Dynamic SQL - which in mssql is accomplished using EXEC(sql string) inside a stored procedure.

MySQL handles this using 'Prepared Statements'. See manual section 13.7. SQL Syntax for Prepared Statements. Prepared statements can be used in MySQL's command line tool, from a client or inside a stored procedure.

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 ;



-- Usiang a PREPAREd statement EXECUTEd with input variables. Use this method if your stored procedure will EXECUTE the PREPAREd statement with different sets of 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');


** When in doubt try your SQL in the MySQL command line client. You can cut and paste blocks of code to the command line client.

** I tried the examples in section 13.7 as a stored procedure and the second one crashed MySQL, so this might be a bit buggy.