UFN_SEARCHLIST_DATAFORMREFERENCES

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

    -- build a temp table containing the data form instance ID

    declare @t table (ID uniqueidentifier);

    -- find forms that use this search list

    with xmlnamespaces ('bb_appfx_commontypes' as common)
    insert into @t
        select distinct INSTANCE.ID
        from dbo.DATAFORMINSTANCECATALOG as INSTANCE
            inner join dbo.DATAFORMTEMPLATECATALOG as TEMPLATE on INSTANCE.DATAFORMTEMPLATECATALOGID = TEMPLATE.ID
            cross apply TEMPLATE.TEMPLATESPECXML.nodes('//common:SearchList') as dataform(searchlistfields)
        where 
            searchlistfields.value('@SearchListID', 'uniqueidentifier') = @SEARCHLISTID;

     -- for add forms, find duplicate record checks that use this search list

        with xmlnamespaces ('bb_appfx_adddataformtemplate' as tns, 'bb_appfx_commontypes' as common)
        insert into @t
            select distinct INSTANCE.ID
            from dbo.DATAFORMINSTANCECATALOG as INSTANCE
                inner join dbo.DATAFORMTEMPLATECATALOG as TEMPLATE on INSTANCE.DATAFORMTEMPLATECATALOGID = TEMPLATE.ID
            where (TEMPLATE.MODE = '2') and
                  (TEMPLATE.TEMPLATESPECXML.value('(tns:AddDataFormTemplateSpec/tns:DuplicateRecordCheck/@SearchListID)[1]', 'uniqueidentifier') = @SEARCHLISTID)
                  and not exists (select ID from @t where ID = INSTANCE.ID);                

     -- for edit forms, find duplicate record checks that use this search list

        with xmlnamespaces ('bb_appfx_editdataformtemplate' as tns, 'bb_appfx_commontypes' as common)
        insert into @t
            select distinct INSTANCE.ID
            from dbo.DATAFORMINSTANCECATALOG as INSTANCE
                inner join dbo.DATAFORMTEMPLATECATALOG as TEMPLATE on INSTANCE.DATAFORMTEMPLATECATALOGID = TEMPLATE.ID
            where (TEMPLATE.MODE = '1') and
                  (TEMPLATE.TEMPLATESPECXML.value('(tns:EditDataFormTemplateSpec/tns:DuplicateRecordCheck/@SearchListID)[1]', 'uniqueidentifier') = @SEARCHLISTID);

    -- find UI Actions that use this search list

    with xmlnamespaces ('bb_appfx_commontypes' as common)
    insert into @t
        select distinct INSTANCE.ID
        from dbo.DATAFORMINSTANCECATALOG as INSTANCE
            inner join dbo.DATAFORMTEMPLATECATALOG as TEMPLATE on INSTANCE.DATAFORMTEMPLATECATALOGID = TEMPLATE.ID
            cross apply TEMPLATE.TEMPLATESPECXML.nodes('//common:ShowSearch') as dataform(showsearchactions)
        where 
            showsearchactions.value('@SearchListID', 'uniqueidentifier') = @SEARCHLISTID;

    -- now build the results

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

    return;

end;