title: recursion in sql server in multi level
body: <p>I want to create stored procedure that call recursion function, each time it will execute update statement.</p>
  
  <p>I have a table called <code>document</code></p>
  
  <pre><code>documentId   folderId
  ----------   --------
  1222         1
  1256         2
  1257         3
  </code></pre>
  
  <p>And a <code>folder</code> table:</p>
  
  <pre><code>folderId    parentFolder
  --------    -----------
  1             5
  2             1
  3             2 
  5             null
  </code></pre>
  
  <p>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.</p>
  
  <p>How to do that?</p>
  
  <pre><code>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&lt;@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
  </code></pre>