Mysql – Dynamic SQL is not allowed error for creating function in MySQL

dynamic-sqlerrorsfunctionsMySQLstored-procedures

According to this question and answer of Angelo I create a MySQL function as below:

DELIMITER $$
CREATE FUNCTION hierarchy_connect_by_parent_eq_prior_id(value INT, reqdTable VARCHAR(50)) RETURNS INT
NOT DETERMINISTIC
READS SQL DATA
BEGIN
            DECLARE _id INT;
            DECLARE _parent INT;
            DECLARE _next INT;
            DECLARE CONTINUE HANDLER FOR NOT FOUND SET @id = NULL;
            SET _parent = @id;
            SET _id = -1;
            SET @table_name = reqdTable;
            IF @id IS NULL THEN
                RETURN NULL;
            END IF;
            LOOP

                SET @sql_text = concat('SELECT MIN(id) INTO @id FROM ', @table_name,' WHERE parent_id = _parent AND id > _id;');
                PREPARE stmt FROM @sql_text;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;

                IF @id IS NOT NULL OR _parent = @start_with THEN
                  SET @level = @level + 1;
                  RETURN @id;
                END IF;
                SET @level := @level - 1;

                SET @sql_text = concat('SELECT id, parent_id INTO _id, _parent FROM ', @table_name,' WHERE id = _parent;');
                PREPARE stmt FROM @sql_text;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;

            END LOOP;     
END

But It has following Error:

 #1336 - Dynamic SQL is not allowed in stored function or trigger

What should I do?

Best Answer

You want to use a stored procedure instead of a stored function

You can rewrite it like this

DELIMITER $$
CREATE PROCEDURE hierarchy_connect_by_parent_eq_prior_id(IN value INT, IN reqdTable VARCHAR(50), OUT result INT)
BEGIN
    DECLARE _id INT;
    DECLARE _parent INT;
    DECLARE _next INT;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET @id = NULL;
    SET _parent = @id;
    SET _id = -1;
    SET @table_name = reqdTable;
    IF @id IS NULL THEN
        SET result =  NULL;
    END IF;
    LOOP

        SET @sql_text = concat('SELECT MIN(id) INTO @id FROM ', @table_name,' WHERE parent_id = _parent AND id > _id;');
        PREPARE stmt FROM @sql_text;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

        IF @id IS NOT NULL OR _parent = @start_with THEN
          SET @level = @level + 1;
          SET result = @id;
        END IF;
        SET @level := @level - 1;

        SET @sql_text = concat('SELECT id, parent_id INTO _id, _parent FROM ', @table_name,' WHERE id = _parent;');
        PREPARE stmt FROM @sql_text;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;

    END LOOP;     
END$$
DELIMITER ;

Then use it

CALL hierarchy_connect_by_parent_eq_prior_id(1, 'table1', @result);
SELECT @result;
Related Question