MySQL Stored routines (procedures and functions) Create Procedure with a Constructed Prepare
Example
DROP PROCEDURE if exists displayNext100WithName;
DELIMITER $$
CREATE PROCEDURE displayNext100WithName
( nStart int,
tblName varchar(100)
)
BEGIN
DECLARE thesql varchar(500); -- holds the constructed sql string to execute
-- expands the sizing of the output buffer to accomodate the output (Max value is at least 4GB)
SET session group_concat_max_len = 4096; -- prevents group_concat from barfing with error 1160 or whatever it is
SET @thesql=CONCAT("select group_concat(qid order by qid SEPARATOR '%3B') as nums ","from ( select qid from ");
SET @thesql=CONCAT(@thesql,tblName," where qid>? order by qid limit 100 )xDerived");
PREPARE stmt1 FROM @thesql; -- create a statement object from the construct sql string to execute
SET @p1 = nStart; -- transfers parameter passed into a User Variable compatible with the below EXECUTE
EXECUTE stmt1 USING @p1;
DEALLOCATE PREPARE stmt1; -- deallocate the statement object when finished
END$$
DELIMITER ;
Creation of the stored procedure shows wrapping with a DELIMITER necessary in many client tools.
Calling example:
call displayNext100WithName(1,"questions_mysql");
Sample output with %3B (semi-colon) separator:
