I am trying to create a stored procedure in HeidiSQL where the database name is dynamic and passed in.
When I try and create it, it says there is an error on this line PREPARE stmt FROM sqlQuery;
.
Here is the full "CREATE code":
CREATE DEFINER=`doadmin`@`%` PROCEDURE `getQuotesForPricelist`(
IN `_fromDBName` VARCHAR(20),
IN `_toDBName` VARCHAR(20),
IN `_pricelistID` MEDIUMINT
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
DECLARE sqlQuery VARCHAR(100);
-- Construct the query dynamically
SET sqlQuery = CONCAT('SELECT * FROM ', _fromDBName, '.PriceLists WHERE id=', _priceListID);
-- Prepare and execute the query
PREPARE stmt FROM sqlQuery;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END
If anyone knows of a better way that I can dynamically pass in a database name, I'm all ears.