USP_SEARCHLIST_DATAFORMINSTANCEATTRIBUTEEXTENSIBLE

CREATE procedure dbo.USP_SEARCHLIST_DATAFORMINSTANCEATTRIBUTEEXTENSIBLE
(
    @NAME nvarchar(100) = null,
    @ATTRIBUTERECORDTYPEID uniqueidentifier = null,
    @MODE smallint = null,
    @MAXROWS smallint = 500
)
as
    set nocount on;

    set @NAME = dbo.UFN_SEARCHCRITERIA_GETLIKEPARAMETERVALUE(@NAME, 0, null);

    select top(@MAXROWS) 
        DATAFORMINSTANCECATALOG.ID,
        DATAFORMINSTANCECATALOG.UINAME [NAME],
        DATAFORMINSTANCECATALOG.DESCRIPTION,
        RECORDTYPE.NAME RECORDTYPE,
        DATAFORMTEMPLATECATALOG.MODENAME MODE
    from dbo.DATAFORMINSTANCECATALOG
    left join dbo.DATAFORMTEMPLATECATALOG on DATAFORMINSTANCECATALOG.DATAFORMTEMPLATECATALOGID = DATAFORMTEMPLATECATALOG.ID
    left join dbo.RECORDTYPE on DATAFORMTEMPLATECATALOG.RECORDTYPEID = RECORDTYPE.ID
    inner join dbo.ATTRIBUTERECORDTYPE on RECORDTYPE.ID = ATTRIBUTERECORDTYPE.RECORDTYPEID
    where 
        /* Exclude data form extensions */
        not exists(select ID from dbo.DATAFORMEXTENSION where DATAFORMEXTENSION.ID = DATAFORMINSTANCECATALOG.ID)
        /* Exclude batch data forms */
        and dbo.UFN_DATAFORMINSTANCE_INUSEBYBATCH(DATAFORMINSTANCECATALOG.ID) = 0
        and (@NAME is null or DATAFORMINSTANCECATALOG.UINAME like @NAME)
        and (@ATTRIBUTERECORDTYPEID is null or ATTRIBUTERECORDTYPE.ID = @ATTRIBUTERECORDTYPEID)
        and (@MODE is null or DATAFORMTEMPLATECATALOG.MODE = @MODE)
        and 1 = dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED
        (
            DATAFORMTEMPLATECATALOG.TEMPLATESPECXML.query
                (
                    'declare namespace common="bb_appfx_commontypes";
                    /*/common:InstalledProductList'
                )
        )
    order by DATAFORMINSTANCECATALOG.UINAME;