USP_DATALIST_USERDEFINEDSMARTQUERY

Provides a list of smart queries 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_USERDEFINEDSMARTQUERY
                (
                    @CURRENTAPPUSERID uniqueidentifier,
                    @RECORDTYPEID uniqueidentifier = null
                )
                as
                    set nocount on;

                        with xmlnamespaces('bb_appfx_smartquery' as ns, 'bb_appfx_commontypes' as c)
                    select 
                        SMARTQUERYCATALOG.ID, 
                        SMARTQUERYCATALOG.UINAME as [NAME], 
                        SMARTQUERYCATALOG.[DESCRIPTION],
                        RECORDTYPE.NAME [RECORDTYPE]
                    from 
                        dbo.SMARTQUERYCATALOG
                        left join dbo.RECORDTYPE on SMARTQUERYCATALOG.RECORDTYPEID = RECORDTYPE.ID
                    where 
                        (@RECORDTYPEID is null or SMARTQUERYCATALOG.RECORDTYPEID = @RECORDTYPEID)
                        and    SMARTQUERYCATALOG.SMARTQUERYSPEC.value('ns:SmartQuerySpec[1]/c:MetaTags[1]/AdHocQuerySaveSmartQueryRequest[1]', 'nvarchar(max)') is not null
                    order by
                        SMARTQUERYCATALOG.UINAME;