UFN_QUERY_DATALISTSUSERDEFINED

Return

Return Type
table

Parameters

Parameter Parameter Type Mode Description
@RECORDTYPEID uniqueidentifier IN

Definition

Copy


                create function dbo.[UFN_QUERY_DATALISTSUSERDEFINED](
                    @RECORDTYPEID uniqueidentifier = null
                )

                    returns @USERDEFINEDDATALISTS table
                    (
                        ID uniqueidentifier,
                        NAME nvarchar(60),
                        DESCRIPTION nvarchar(1000),
                        RECORDTYPE nvarchar(50)
                    )

                with execute as caller
                as
                begin
                        with xmlnamespaces('bb_appfx_datalist' as ns, 'bb_appfx_commontypes' as c)

                        insert into @USERDEFINEDDATALISTS(
                            ID,
                            NAME,
                            DESCRIPTION,
                            RECORDTYPE
                        )
                        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.exist('ns:DataListSpec[1]/c:MetaTags[1]/AdHocQuerySaveDataListRequest[1]') = 1
                        order by DATALISTCATALOG.UINAME;

                        return;
                    end