USP_DATALIST_GETLIST

Lists data lists from the catalog.
Parameters

Parameter	Parameter Type	Mode
@FilterRecordType	nvarchar(50)	IN
@FilterNavigationSpecsOnly	bit	IN
@MaxRows	int	IN
Definition

Copy


CREATE procedure [dbo].[USP_DATALIST_GETLIST]
(
  @FilterRecordType nvarchar(50) = '',
  @FilterNavigationSpecsOnly bit = 0,
  @MaxRows int = 0
)
as
    set nocount on;

    if (@FilterNavigationSpecsOnly = 1)
        with XMLNAMESPACES ('bb_appfx_datalist' as c)
        select
            DATALISTCATALOG.ID, DATALISTCATALOG.NAME, DATALISTCATALOG.DESCRIPTION, COALESCE(RECORDTYPE.NAME, '') as 'RECORDTYPE', DATALISTCATALOG.UINAME
        from
            dbo.DATALISTCATALOG
            left outer join dbo.RECORDTYPE on DATALISTCATALOG.RECORDTYPEID = RECORDTYPE.ID
        where
            ((@FilterRecordType = '') or (RECORDTYPE.NAME = @FilterRecordType)) and
            ((DATALISTCATALOG.DATALISTSPEC.exist('/c:DataListSpec/c:Output/c:OutputFields/c:OutputField[upper-case(@FieldID)="CAPTION"]') = 1) and
            ((DATALISTCATALOG.DATALISTSPEC.exist('/c:DataListSpec/c:Output/c:OutputFields/c:OutputField[upper-case(@FieldID)="URL"]') = 1) or
            ((DATALISTCATALOG.DATALISTSPEC.exist('/c:DataListSpec/c:Output/c:OutputFields/c:OutputField[upper-case(@FieldID)="PAGEID"]') = 1) and
            (DATALISTCATALOG.DATALISTSPEC.exist('/c:DataListSpec/c:Output/c:OutputFields/c:OutputField[upper-case(@FieldID)="CONTEXTID"]') = 1))))
        order by
            DATALISTCATALOG.UINAME;
    else
        if (@MaxRows > 0)
        select top (@MaxRows)  
            DATALISTCATALOG.ID, DATALISTCATALOG.NAME, DATALISTCATALOG.DESCRIPTION, COALESCE(RECORDTYPE.NAME, '') as 'RECORDTYPE', DATALISTCATALOG.UINAME
        from  
            dbo.DATALISTCATALOG  
            left outer join dbo.RECORDTYPE on DATALISTCATALOG.RECORDTYPEID = RECORDTYPE.ID
        where
            ((@FilterRecordType = '') or (RECORDTYPE.NAME = @FilterRecordType))
        order by
            DATALISTCATALOG.UINAME;
    else
        select
            DATALISTCATALOG.ID, DATALISTCATALOG.NAME, DATALISTCATALOG.DESCRIPTION, COALESCE(RECORDTYPE.NAME, '') as 'RECORDTYPE', DATALISTCATALOG.UINAME
        from  
            dbo.DATALISTCATALOG  
            left outer join dbo.RECORDTYPE on DATALISTCATALOG.RECORDTYPEID = RECORDTYPE.ID
        where  
            ((@FilterRecordType = '') or (RECORDTYPE.NAME = @FilterRecordType))
        order by
         DATALISTCATALOG.UINAME;

    return 0;