#TmpDistinctFolderIds table contains one or many
fnGetAllParentFolderIds function receives the input as
FolderId and return it's all possible parent folder ids (a parent folder can have N number of sub folders).
I'm using the below block to get each folder's all possible hierarchy parent folder ids. This approach is working as expected. No issues with the approach.
But can I achieve the same with out using
WHILE execution or any other better approach?
DECLARE @TmpFolderId INT; DECLARE @RelatedParentFolderIds TABLE (FolderID INT, ParentFolderID INT); WHILE EXISTS (SELECT * FROM #TmpDistinctFolderIds) BEGIN SELECT TOP 1 @TmpFolderId = FolderId FROM #TmpDistinctFolderIds -- Get all possible hierarchy level parent folder ids INSERT INTO @RelatedParentFolderIds (FolderID, ParentFolderID) SELECT FolderID, ParentFolderID FROM [dbo].[fnGetAllParentFolderIds] (@TmpFolderId) DELETE FROM #TmpDistinctFolderIds WHERE FolderId = @TmpFolderId END