UFN_ADHOCQUERY_GETANCESTORS

Returns the ancestor folders for the given folder.
Return

Return Type
table
Parameters

Parameter	Parameter Type	Mode	Description
@FOLDERID	uniqueidentifier	IN
Definition

Copy


            CREATE function dbo.UFN_ADHOCQUERY_GETANCESTORS
            (
                @FOLDERID uniqueidentifier
            )
            returns table
            as 
            return
            (
                with HIERARCHYCTE as
                (
                    select
                        PARENTFOLDERID,
                        ID,
                        0 as [LEVEL],
                        APPLYASDEFAULTPERMISSIONSFORALLSUBFOLDERS
                    from dbo.ADHOCQUERYFOLDER
                    where
                        ID = @FOLDERID

                    union all

                    select
                        ADHOCQUERYFOLDER.PARENTFOLDERID,
                        ADHOCQUERYFOLDER.ID,
                        HIERARCHYCTE.[LEVEL] + 1,
                        ADHOCQUERYFOLDER.APPLYASDEFAULTPERMISSIONSFORALLSUBFOLDERS
                    from dbo.ADHOCQUERYFOLDER
                    inner join HIERARCHYCTE on ADHOCQUERYFOLDER.ID = HIERARCHYCTE.PARENTFOLDERID
                )
                select
                    ID,
                    LEVEL,
                    APPLYASDEFAULTPERMISSIONSFORALLSUBFOLDERS
                from HIERARCHYCTE
            )