UFN_PAGE_TASKREFERENCES

CREATE function dbo.UFN_PAGE_TASKREFERENCES
(
    @PAGEID uniqueidentifier
)
returns @TABLE table
(
    TASKID uniqueidentifier
)
with execute as caller
as
begin

    -- build a temp table containing the page ID

    declare @t table (ID uniqueidentifier);

    with xmlnamespaces ('bb_appfx_task' as tns, 'bb_appfx_commontypes' as common)
    insert into @t
        select T.ID
        from dbo.TASKCATALOG as T
        where
            -- include any task with a PostAction that navigates to this page

            T.TASKSPECXML.value('(//common:PostActionEvent/common:GoToPage/common:SpecificPage/@PageID)[1]', 'uniqueidentifier') = @PAGEID

            -- include BrowseQueryResults tasks that utilize this page

            or
            T.TASKSPECXML.value('(tns:TaskSpec/common:BrowseQueryResults/@PageID)[1]', 'uniqueidentifier') = @PAGEID

            -- include RunBusinessProcess tasks that utilize this page

            or 
            T.TASKSPECXML.value('(tns:TaskSpec/common:RunBusinessProcess/@PageID)[1]', 'uniqueidentifier') = @PAGEID

            -- include ShowPage tasks that navigate to the page

            or
            T.TASKSPECXML.value('(tns:TaskSpec/common:ShowPage/@PageID)[1]', 'uniqueidentifier') = @PAGEID

    -- now build the results

    insert into @TABLE
        select distinct T.ID
        from @t as T

    return;

end;