UFN_QUERYVIEW_GETFIELDCAPTION

Returns the field caption for a given field in a query view.

Return

Return Type
nvarchar(255)

Parameters

Parameter	Parameter Type	Mode	Description
@QUERYVIEWCATALOGID	uniqueidentifier	IN
@FIELDNAME	nvarchar(255)	IN

Definition

Copy



CREATE function dbo.[UFN_QUERYVIEW_GETFIELDCAPTION]
(
  @QUERYVIEWCATALOGID uniqueidentifier,
  @FIELDNAME nvarchar(255)
)
returns nvarchar(255)
as
begin
  declare @OUTPUTDEFINITIONXML xml;

  select
    @OUTPUTDEFINITIONXML = [Q].[OUTPUTDEFINITIONXML]
  from
    dbo.[QUERYVIEWCATALOG] [Q]
  where
    [Q].[ID] = @QUERYVIEWCATALOGID;

  return 
  (
    select top 1
      isnull(T.c.value('(@Caption)[1]','nvarchar(255)'), T.c.value('(@Name)[1]','nvarchar(255)'))
    from
      @OUTPUTDEFINITIONXML.nodes('declare namespace QV="bb_appfx_queryview";/QueryViewOutput/QV:OutputFields/QV:OutputField') T(c)
    where
      T.c.value('(@Name)[1]','nvarchar(255)') = @FIELDNAME
    order by
      row_number() over(order by T.c.value('(@Name)[1]','nvarchar(255)')) desc
  );
end