Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
That question was asking how to implement dynamic SQL on a stored procedure, and you are trying to use it on a function (and, as the error says, you can't use dynamic SQL on a function). –  Lamak Aug 16 '13 at 21:14
    
Bravo, you understand! So what should I Do? –  Arash Mousavi Aug 17 '13 at 4:58
add comment

1 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;
share|improve this answer
    
But I want to used in select statement, there isn't any way? –  Arash Mousavi Aug 17 '13 at 8:40
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.