0
\$\begingroup\$

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
\$\endgroup\$
1
\$\begingroup\$

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
\$\endgroup\$
0
\$\begingroup\$

In order to avoid the WHILE loop, you should create your own table variable type, pass it as a parameter to your function and, finally, modify the function's logic accordingly .

http://www.techrepublic.com/blog/the-enterprise-cloud/passing-table-valued-parameters-in-sql-server-2008/

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.