USP_DATAFORMINSTANCE_GETLIST

Lists data form instances from the catalog.
Parameters

Parameter	Parameter Type	Mode
@FilterMode	int	IN
@FilterRecordType	nvarchar(50)	IN
@NAMEFILTER	nvarchar(1000)	IN
@MaxRows	int	IN
Definition

Copy


CREATE procedure dbo.USP_DATAFORMINSTANCE_GETLIST
    @FilterMode int = -1,
    @FilterRecordType nvarchar(50) = '',
    @NAMEFILTER nvarchar(1000) = '',
    @MaxRows int = 0
as
    set nocount on;

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

    if(@MaxRows>0)
        begin
            select top(@MaxRows)
                INSTANCE.ID as 'INSTANCEID',
                INSTANCE.FORMNAME as 'INSTANCENAME',
                INSTANCE.DESCRIPTION as 'INSTANCEDESC',
                TEMPLATE.ID as 'TEMPLATEID',
             TEMPLATE.TEMPLATENAME as 'TEMPLATENAME',
                TEMPLATE.MODE as 'MODE',
                COALESCE(RECORDTYPE.NAME, '') as 'RECORDTYPE',
                RECORDTYPE.ID as 'RECORDTYPEID',
             INSTANCE.UINAME as 'INSTANCEUINAME',
                TEMPLATE.UINAME as 'TEMPLATEUINAME'
         from 
                dbo.DATAFORMINSTANCECATALOG as INSTANCE
                inner join dbo.DATAFORMTEMPLATECATALOG as TEMPLATE on INSTANCE.DATAFORMTEMPLATECATALOGID = TEMPLATE.ID
             left outer join dbo.RECORDTYPE on TEMPLATE.RECORDTYPEID = RECORDTYPE.ID
         where 
                ((@FilterMode = -1) or (TEMPLATE.MODE = @FilterMode)) and
                ((@FilterRecordType = '') or (RECORDTYPE.NAME = @FilterRecordType)) and
                ((isnull(@NAMEFILTER,'') = '') or (INSTANCE.UINAME like @NAMEFILTER))

        order by INSTANCE.UINAME;
        end
    else
        begin
            select
                INSTANCE.ID as 'INSTANCEID',
                INSTANCE.FORMNAME as 'INSTANCENAME',
                INSTANCE.DESCRIPTION as 'INSTANCEDESC',
                TEMPLATE.ID as 'TEMPLATEID',
            TEMPLATE.TEMPLATENAME as 'TEMPLATENAME',
                TEMPLATE.MODE as 'MODE',
                COALESCE(RECORDTYPE.NAME, '') as 'RECORDTYPE',
                RECORDTYPE.ID as 'RECORDTYPEID',
            INSTANCE.UINAME as 'INSTANCEUINAME',
                TEMPLATE.UINAME as 'TEMPLATEUINAME'
         from 
                dbo.DATAFORMINSTANCECATALOG as INSTANCE
                inner join dbo.DATAFORMTEMPLATECATALOG as TEMPLATE on INSTANCE.DATAFORMTEMPLATECATALOGID = TEMPLATE.ID
             left outer join dbo.RECORDTYPE on TEMPLATE.RECORDTYPEID = RECORDTYPE.ID
         where
            ((@FilterMode = -1) or (TEMPLATE.MODE = @FilterMode)) and
            ((@FilterRecordType = '') or (RECORDTYPE.NAME = @FilterRecordType)) and
            ((isnull(@NAMEFILTER,'') = '') or (INSTANCE.UINAME like @NAMEFILTER))

         order by INSTANCE.UINAME;
        end


    return 0;