Create Stored Procedure with dynamic query in prepared statement

dmikester1's profile image dmikester1 posted 22 hours ago in General Permalink

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.

Please login to leave a reply, or register at first.