USP_DATALIST_USERDEFINEDDATALIST

Provides a list of data lists that were created from an ad-hoc query.

Parameters

Parameter	Parameter Type	Mode	Description
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@RECORDTYPEID	uniqueidentifier	IN	Record type

Definition

Copy



CREATE procedure dbo.USP_DATALIST_USERDEFINEDDATALIST(@CURRENTAPPUSERID uniqueidentifier,
    @RECORDTYPEID uniqueidentifier = null)
as
    set nocount on;
        with xmlnamespaces('bb_appfx_datalist' as ns, 'bb_appfx_commontypes' as c)
    select DATALISTCATALOG.ID, 
        DATALISTCATALOG.UINAME as [NAME], 
        DATALISTCATALOG.[DESCRIPTION],
        RECORDTYPE.NAME [RECORDTYPE]
    from dbo.DATALISTCATALOG
    left join dbo.RECORDTYPE on DATALISTCATALOG.RECORDTYPEID = RECORDTYPE.ID
    where IMPLEMENTATIONTYPE = 0 /* A data list based on an ad-hoc query can only be an SP-implemented data list, so use this as a filter to limit the number of DATALISTSPEC XML values needed to query */ 
        and (@RECORDTYPEID is null or DATALISTCATALOG.RECORDTYPEID = @RECORDTYPEID)
        and    DATALISTCATALOG.DATALISTSPEC.value('ns:DataListSpec[1]/c:MetaTags[1]/AdHocQuerySaveDataListRequest[1]', 'nvarchar(max)') is not null
    order by DATALISTCATALOG.UINAME;