USP_SIMPLEDATALIST_MKTCONSOLIDATEDQUERYVIEWSPEC

CREATE procedure dbo.[USP_SIMPLEDATALIST_MKTCONSOLIDATEDQUERYVIEWSPEC]
as
  set nocount on;

  /* Get all available record source query views.  If a consolidated query view exists for a */
  /* record source, then return it, else return the original query view. */
  select
    [QUERYVIEWCATALOG].[ID] as [VALUE],
    [QUERYVIEWCATALOG].[DISPLAYNAME] as [LABEL]
  from
    dbo.[MKTRECORDSOURCE]
  left outer join
    dbo.[MKTCONSOLIDATEDQUERYVIEWSPEC]
  on
    [MKTCONSOLIDATEDQUERYVIEWSPEC].[ID] = [MKTRECORDSOURCE].[ID]
  inner join
    dbo.[QUERYVIEWCATALOG]
  on
    [QUERYVIEWCATALOG].[ID] = isnull([MKTCONSOLIDATEDQUERYVIEWSPEC].[CONSOLIDATEDQUERYVIEWCATALOGID], [MKTRECORDSOURCE].[ID])
  where dbo.[UFN_MKTRECORDSOURCE_VALIDFORPRODUCT](isnull([MKTCONSOLIDATEDQUERYVIEWSPEC].[CONSOLIDATEDQUERYVIEWCATALOGID], [MKTRECORDSOURCE].[ID])) = 1
  order by
    [QUERYVIEWCATALOG].[DISPLAYNAME];

  return 0;