USP_DATALIST_MKTSELECTION

CREATE procedure dbo.[USP_DATALIST_MKTSELECTION]
(
  @CURRENTAPPUSERID uniqueidentifier, 
  @RECORDTYPEID uniqueidentifier = null, 
  @ADHOC bit = 1,
  @SMART bit = 1,
  @OTHER bit = 0,
  @QUERYCATEGORYCODEID uniqueidentifier = null, 
  @QUERYVIEWCATALOGID uniqueidentifier = null
)
as
  set nocount on;

  select 
    [IDSR].[ID] as [SELECTIONID],
    [IDSR].[name] as [SELECTIONNAME],
    coalesce(coalesce([AQ].[NAME], SQ.[NAME]), '') as [SOURCEQUERYNAME],
    case when ([AQ].[ID] is not null) then 'Ad-hoc'
         when ([SQ].[ID] is not null) then 'Smart'
         else 'Other' 
    end as [SOURCEQUERYTYPE],
    coalesce((select [NAME] from dbo.[RECORDTYPE] where [ID] = [IDSR].[RECORDTYPEID]), '') as [RECORDTYPE],
    coalesce(coalesce((select [DESCRIPTION] from dbo.[QUERYCATEGORYCODE] where [ID] = [AQ].[QUERYCATEGORYCODEID]),
                      (select [DESCRIPTION] from dbo.[QUERYCATEGORYCODE] where [ID] = [SQ].[QUERYCATEGORYCODEID])), '') 
    as [CATEGORY],
    case [IDSR].[STATIC] when 1 then 'Static' else 'Dynamic' end as [SELECTIONTYPE],
    [AQ].[ID] as [ADHOCQUERYID],
    [SQ].[ID] as [SMARTQUERYID],
    [IDSR].[DESCRIPTION]
  from dbo.[IDSETREGISTER] as [IDSR]
  left outer join dbo.[IDSETREGISTERADHOCQUERY] as [IDSRA] on [IDSR].[ID] = [IDSRA].[IDSETREGISTERID]
  -- ad hoc query joins

  left outer join dbo.[ADHOCQUERY] as [AQ] on [IDSRA].[ADHOCQUERYID] = [AQ].[ID]
  left outer join dbo.[QUERYVIEWCATALOG] as [QVC] on [AQ].[QUERYVIEWCATALOGID] = [QVC].[ID]
  -- smart query joins

  left outer join dbo.[IDSETREGISTERSMARTQUERYINSTANCE] as [IDSRS] on [IDSR].[ID] = [IDSRS].[IDSETREGISTERID]
  left outer join dbo.[SMARTQUERYINSTANCE] as [SQ] on [IDSRS].[SMARTQUERYINSTANCEID] = [SQ].[ID]
  left outer join dbo.[SMARTQUERYCATALOG] as [SQC] on [SQ].[SMARTQUERYCATALOGID] = [SQC].[ID]
  where
    (@RECORDTYPEID is null or [IDSR].[RECORDTYPEID] = @RECORDTYPEID)
  and 
    (@ADHOC = 1 or [AQ].[ID] is null)
  and 
    (@SMART = 1 or [SQ].[ID] is null)
  and
    (@OTHER = 1 or ([AQ].[ID] is not null or [SQ].[ID] is not null))
  and 
    (@QUERYVIEWCATALOGID is null or [QVC].[ID] = @QUERYVIEWCATALOGID)
  and
    ([IDSR].[ISSYSTEM] = 0)
  order by
    coalesce(coalesce((select [DESCRIPTION] from dbo.[QUERYCATEGORYCODE] where [ID] = [AQ].[QUERYCATEGORYCODEID]),
                      (select [DESCRIPTION] from dbo.[QUERYCATEGORYCODE] where [ID] = [SQ].[QUERYCATEGORYCODEID])), ''),
    [IDSR].[NAME];

  return 0;