MySql :: stored procedure recursive

Question

how can I rewrite the following SP to face recursive limitation in MySQL:

CREATE DEFINER = `root`@`localhost` PROCEDURE `SP_DeleteParentDirectory`(pParentID INT,pIsFolder INT,pReferenceID INT)
BEGIN   
SET @@SESSION.max_sp_recursion_depth = 255;
    SET @IsFolder= NULL;
    SET @ChildID= NULL;
    SET @ReferenceID= NULL;
    IF ( pIsFolder= 2) THEN
                SELECT  FileID INTO @ChildID FROM filesinfo WHERE ParentID= pParentID LIMIT 1;
                WHILE (@ChildID IS NOT NULL) DO
                         SELECT  filesinfo.IsFolder,filesinfo.ReferenceID INTO @IsFolder,@ReferenceID FROM filesinfo WHERE filesinfo.FileID = @ChildID;
                        CALL SP_DeleteParentDirectory(@ChildID,@IsFolder,@ReferenceID);             
                        SELECT filesinfo.FileID INTO @ChildID FROM filesinfo WHERE ParentID= pParentID LIMIT 1;
                END While;
    END IF;
    DELETE FROM filesinfo Where  filesinfo.FileID= pParentID;
    IF EXISTS( SELECT  * FROM  filesrefrences WHERE filesrefrences.ReferenceID = pReferenceID AND filesrefrences.RefrenceCount>1) THEN
            UPDATE filesrefrences
            SET RefrenceCount= RefrenceCount-1 WHERE  filesrefrences.ReferenceID = pReferenceID;            
    ELSE            
            SET @ReferenceKey= NULL;
            Select filesrefrences.RefrenceKey INTO @ReferenceKey FROM filesrefrences WHERE filesrefrences.ReferenceID= pReferenceID;
            IF NOT EXISTS(SELECT * FROM deletefilesreferences WHERE deletefilesreferences.FileReference= @ReferenceKey) THEN
            INSERT INTO deletefilesreferences (deletefilesreferences.FileReference)VALUES(@ReferenceKey);
            END IF;
            DELETE FROM filesrefrences where filesrefrences.ReferenceID = pReferenceID;         
    END IF;
    -- CALL SP_DeleteFileInfo(pParentID,pReferenceID);      
    -- SELECT * FROM deletefilesreferences;
    -- CALL SP_GetDeletedReferences();
END;

without using this sts :

"SET @@SESSION.max_sp_recursion_depth = 255;" I mean that can interact with hierarchy data without recursion.

Jirilmon · Accepted Answer · 2013-01-14 11:08:42Z

2

Mysql allows up to 255 recursion in a PROCEDURE if you set max_sp_recursion_depth.

Its risky because there is a chance that the needed recursion depth may exceed 255 sometime in future.

So Its better you replace your function with a procedure, using an INOUT variable for the return_result.

answered Jan 14, 2013 at 11:08

Jirilmon

1,9441 gold badge12 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySql :: stored procedure recursive

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related