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