UFN_DATAFORMINSTANCE_TASKREFERENCES

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

    -- build a temp table containing the task ID

    declare @t table (ID uniqueidentifier);

    declare @mode smallint;
    set @mode = (select TEMPLATE.MODE  
                 from dbo.DATAFORMTEMPLATECATALOG TEMPLATE inner join dbo.DATAFORMINSTANCECATALOG INSTANCE on INSTANCE.DATAFORMTEMPLATECATALOGID = TEMPLATE.ID
                 where INSTANCE.ID = @DATAFORMINSTANCEID);

    -- for add forms, we need to look for ShowAddForm actions

    if @mode = 2
        begin
            with xmlnamespaces ('bb_appfx_task' as tns, 'bb_appfx_commontypes' as common)
            insert into @t
                select T.ID
                from dbo.TASKCATALOG as T
                where T.TASKSPECXML.value('(tns:TaskSpec/common:ShowAddDataForm/@DataFormID)[1]', 'uniqueidentifier') = @DATAFORMINSTANCEID;
        end

    -- for edit/view forms, look for ShowDataForm actions

    else if (@mode = 0) or (@mode = 1)
        begin
            with xmlnamespaces ('bb_appfx_task' as tns, 'bb_appfx_commontypes' as common)
            insert into @t
                select T.ID
                from dbo.TASKCATALOG as T
                where T.TASKSPECXML.value('(tns:TaskSpec/common:ShowDataForm/@DataFormID)[1]', 'uniqueidentifier') = @DATAFORMINSTANCEID;
        end;

    -- now build the results

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

    return;

end;