USP_SIMPLEDATALIST_MKTCONSOLIDATEDQUERYVIEWFIELDS

CREATE procedure dbo.USP_SIMPLEDATALIST_MKTCONSOLIDATEDQUERYVIEWFIELDS
(
  @QUERYVIEWCATALOGID uniqueidentifier,
  @SHOWHIDDEN bit = 0
)
as
  set nocount on;

  declare @OUTPUTDEFINITIONXML xml;

  if @SHOWHIDDEN is null
    set @SHOWHIDDEN = 0;

  -- the ID being passed in is actually the queryviewcatalogid of the marketing record source

  -- so lookup the consolidated queryviewcatalogid

  declare @CONSOLIDATEDQUERYVIEWCATALOGID uniqueidentifier;

  select
    @CONSOLIDATEDQUERYVIEWCATALOGID = [CONSOLIDATEDQUERYVIEWCATALOGID]
  from
    dbo.[MKTCONSOLIDATEDQUERYVIEWSPEC]
  where
    [ID] = @QUERYVIEWCATALOGID;

  -- if @CONSOLIDATEDQUERYVIEWCATALOGID is null, then no lists have been imported yet.

  if @CONSOLIDATEDQUERYVIEWCATALOGID is null
    set @CONSOLIDATEDQUERYVIEWCATALOGID = @QUERYVIEWCATALOGID;

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

  select
    T.c.value('(@Name)[1]','nvarchar(255)') as [VALUE],
    -- NCD - 9/13/06 - CR253463-090106 - Changed the [LABEL] to include the category of the field if it exists.

    case when Len(T.c.value('(@Category)[1]','nvarchar(255)')) > 0 then T.c.value('(@Category)[1]','nvarchar(255)') + '/' + isnull(T.c.value('(@Caption)[1]','nvarchar(255)'), T.c.value('(@Name)[1]','nvarchar(255)')) else isnull(T.c.value('(@Caption)[1]','nvarchar(255)'), T.c.value('(@Name)[1]','nvarchar(255)')) end as [LABEL]
  from
    @OUTPUTDEFINITIONXML.nodes('declare namespace QV="bb_appfx_queryview";/QueryViewOutput/QV:OutputFields/QV:OutputField') T(c)
  where
    (@SHOWHIDDEN = 1 or isnull(T.c.value('(@IsHidden)[1]', 'nvarchar(5)'),'0') in ('false', '0'))
  order by
    [LABEL];

  return 0;