CODEWITHSUNDEEP
c#sql-servert-sql
Haneen
Haneen

Reputation: 23

recursion in sql server in multi level

I want to create stored procedure that call recursion function, each time it will execute update statement.

I have a table called document

documentId   folderId
----------   --------
1222         1
1256         2
1257         3

And a folder table:

folderId    parentFolder
--------    -----------
1             5
2             1
3             2 
5             null

My stored procedure will delete folder number (1), when it has been deleted, it should move all document in folder 1 and sub folder of 1 to the parent folder of 1.

How to do that?

CREATE FUNCTION fn_deleteSubFolderDocument
(
@folderId INT ,
@newFolderId INT
)
RETURNS NVARCHAR(100) 
AS
BEGIN
DECLARE @count2 int 
SET @count2=(SELECT COUNT(*) FROM dbo.tbl_document_folder WHERE parent_folder=@folderId)
UPDATE tbl_document SET folder_id=@newFolderId WHERE folder_id=@folderId

IF(@count2 !=0)
BEGIN
DECLARE @table TABLE(id INT IDENTITY(1,1),folderId INT,parentFolder int  )
INSERT INTO @table(folderId,parentFolder)
SELECT folder_id,parent_folder FROM dbo.tbl_document_folder WHERE parent_folder=folder_id
DECLARE @index INT =0
WHILE @index<@count2
BEGIN
SET @index=@index+1
RETURN fn_deleteSubFolderDocument((SELECT folderId FROM @table WHERE id=@index),(SELECT parentFolder FROM @table WHERE id=@index ))
END

END 

END
GO

Upvotes: 1

Views: 1990

Answers (3)

whytheq
whytheq

Reputation: 35577

I'm trying to replicate in SQLfiddle to try to walk down through the tree of dependencies you've illustrated as I think your problem is similar to this SO Answer

ok - this will walk down your tree for you:

create table document
(
  [documentId] int,
  [folderId] int
)
insert into document
values
(1222, 1),
(1256, 2),
(1257, 3)


create table folder
(
  [folderId] int,
  [parentFolder] int null
)
insert into folder
values
(1, 5),
(2, 1),
(3, 2),
(5, null)

Here is the recirsive CTE that walks down the tree and finds the subfolders of subfolders of subfolders ...

DECLARE @folderToDelete int = 1

;WITH RESULT (folders,LEVEL)
  AS
  (
  --anchor
   SELECT
      folderId [folders]
      ,0 AS LEVEL
   FROM folder AS E 
   WHERE folderId = @folderToDelete

  UNION ALL
  SELECT 
      E.folderId [folders]
      ,LEVEL +1 --switched parent/child
  FROM 
      folder AS E
      INNER JOIN RESULT AS D  
         ON 
          E.parentFolder=D.folders 
  --WHERE LEVEL < 100
  )
SELECT *
FROM RESULT OPTION (MAXRECURSION 100)

I've saved a live example here on SQLfiddle

Once you have this set of folders it should be easy enough to find all the associated documents that you need to move

Referring to this article by Pinal Dave I've amended the way the loop detects it's maximum. Without any sort of catch for the maximum if the loop carried on past 100 iterations the server would error but according to this article it looks like MAXRECURSION can be set as high as 32767....hopefully your file structure isn't that complex!

Upvotes: 1

YavgenyP
YavgenyP

Reputation: 2123

Three steps:

  1. select the tree of the descendants, using theenter link description here recursive common table expression
  2. update all the documents, which folder is contained in the provded list
  3. delete the old folder.

Of course, there are opened issues which you didnt answer, such as what to do with the sub-folders themsevles, but it should be easy to solve, using the same recursive CTE technique

--create recursive cte
with childfolders(folderID, parentFolder)
as
{
    --stop when you reach the first level
    select * from folder where parentFolderID = @oldFolderId

    --get the other levels
    select folder.folderId, folder.parentFolderId
     from folder folder inner join childFolders children on children.folderId
     = folder.parentFolderId
}

--update
update document set folderId = @newFolderId
where folderId in (select folderId from childFolders)

--delete
delete from folder where folderId= @oldFolderId

Upvotes: 0

Furqan Hameedi
Furqan Hameedi

Reputation: 4400

  1. you did not mention the case, what if there is no parent folder i-e folder1 has parent id null?

  2. Since this is in the database and you are only deleting folders, merely few update statements shall be enough for the work 

    // Update the documents parent which were in folder1   
Update document SET folderId=@newFolderID Where folderID = @oldFolderID 

// Update the parent in Folder table where parentid =1 
Update folder SET parentId=@newFolderID Where parentID = @oldFolderID 

// Update the folder in Folder table where parentid =1 
Update folder SET folderId=@newFolderID Where folderID = @oldFolderID

except for these does not handle a folder without parent.

Upvotes: 0

Related Questions