0

I use recursion in the following stored procedure 

CREATE  PROCEDURE `SP_DeleteParentDirectory`(pParentID INT,pIsFolder INT,pReferenceID INT)
BEGIN       
    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  @IsFolder := filesinfo.IsFolder, @ReferenceID :=filesinfo.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;
    CALL SP_DeleteFileInfo(pParentID,pReferenceID);     
END;;

but when I try to execute it I face an error in recursion max limit, this is forcing me to add the following statement :

SET @@SESSION.max_sp_recursion_depth = 100;

can I solve the problem without add this statement

Raed Alsaleh
  • 1,581
  • 9
  • 27
  • 50
  • You could add that setting to your my.cnf file – Steven Moseley Jan 13 '13 at 14:40
  • but I don't Know the much of depth I need? I cant specified it ! – Raed Alsaleh Jan 13 '13 at 14:43
  • 1
    MySQL has very limited support for recursive procedures, and no support whatsoever for recursive functions; it is therefore not very well suited to processing hierarchical data stored in the adjacency-list format (such as this). You would do well to model your data differently, such as with nested sets or a transitive closure table. See [this answer](http://stackoverflow.com/a/192462/623041) for more information. – eggyal Jan 13 '13 at 15:02

0 Answers0