Get parent folder ids by folder id(s) My Scenario:

#TmpDistinctFolderIds table contains one or many FolderId.
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
This is typically something you do with a cursor:

DECLARE @tmpFolderId INT;
DECLARE @RelatedParentFolderIds TABLE (FolderID INT, ParentFolderID INT);

DECLARE folderCurs CURSOR LOCAL FAST_FORWARD FOR SELECT FolderID FROM #TmpDistinctFolderIds;
OPEN folderCurs
FETCH NEXT FROM folderCurs INTO @tmpFolderId
WHILE @@FETCH_STATUS = 0
BEGIN
    -- Get all possible hierarchy level parent folder ids
    INSERT INTO @RelatedParentFolderIds (FolderID, ParentFolderID)
    SELECT FolderID, ParentFolderID
    FROM [dbo].[fnGetAllParentFolderIds] (@tmpFolderId)

    FETCH NEXT FROM folderCurs INTO @tmpFolderId
END