USP_DATAFORMTEMPLATE_VIEW_MARKETINGSELECTIONSEARCHRESULTS

CREATE procedure dbo.[USP_DATAFORMTEMPLATE_VIEW_MARKETINGSELECTIONSEARCHRESULTS]
(
  @ID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @NAME nvarchar(300) = null output,
  @DESCRIPTION nvarchar(1024) = null output,
  @RECORDTYPE nvarchar(50) = null output,
  @RECORDTYPEID uniqueidentifier = null output,
  @QUERYVIEWID uniqueidentifier = null output,
  @QUERYID uniqueidentifier = null output,
  @QUERYTYPECODE tinyint = null output,
  @QUERYTYPE nvarchar(20) = null output,
  @USERCANEDIT bit = null output,
  @CURRENTAPPUSERID uniqueidentifier = null
)
as
  set nocount on;

  declare @ISSYSADMIN bit;

  select
    @ISSYSADMIN = [ISSYSADMIN]
  from dbo.[APPUSER]
  where [ID] = @CURRENTAPPUSERID;

  -- be sure to set this, in case the select returns no rows
  set @DATALOADED = 0;

  select distinct top 1
    @DATALOADED = 1,
    @NAME = [IDSETREGISTER].[NAME],
    @DESCRIPTION = [IDSETREGISTER].[DESCRIPTION],
    @RECORDTYPE = [RECORDTYPE].[NAME],
    @RECORDTYPEID = [RECORDTYPE].[ID],
    @QUERYVIEWID = coalesce([ADHOCQUERY].[QUERYVIEWCATALOGID], [SMARTQUERYINSTANCE].[SMARTQUERYCATALOGID], [MKTSEGMENTLIST].[QUERYVIEWCATALOGID]),
    @QUERYID = isnull([ADHOCQUERY].[ID], [SMARTQUERYINSTANCE].[ID]),
    @QUERYTYPECODE = (case when [ADHOCQUERY].[ID] is not null then 1 when [SMARTQUERYINSTANCE].[ID] is not null then 2 else 0 end),
    @QUERYTYPE = (case when [ADHOCQUERY].[ID] is not null then 'Ad-hoc' when [SMARTQUERYINSTANCE].[ID] is not null then 'Smart' else 'Other' end),
    @USERCANEDIT = (case
                      when @ISSYSADMIN = 1 then cast(1 as bit)
                      when [ADHOCQUERY].[ID] is not null and dbo.[UFN_SECURITY_APPUSER_GRANTED_ADHOCQUERYINSTANCEEDIT_IN_SYSTEMROLE](@CURRENTAPPUSERID, [ADHOCQUERY].[ID]) = 1 then cast(1 as bit)
                      when [SMARTQUERYINSTANCE].[ID] is not null and ([SMARTQUERYINSTANCE].[OTHERSCANMODIFY] = 1 or [SMARTQUERYINSTANCE].[OWNERID] = @CURRENTAPPUSERID) then cast(1 as bit)
                      else cast(0 as bit)
                    end)
  from dbo.[IDSETREGISTER]
  inner join dbo.[RECORDTYPE] on [RECORDTYPE].[ID] = [IDSETREGISTER].[RECORDTYPEID]
  left join dbo.[IDSETREGISTERADHOCQUERY] on [IDSETREGISTERADHOCQUERY].[IDSETREGISTERID] = [IDSETREGISTER].[ID]
  left join dbo.[ADHOCQUERY] on [ADHOCQUERY].[ID] = [IDSETREGISTERADHOCQUERY].[ADHOCQUERYID]
  left join dbo.[IDSETREGISTERSMARTQUERYINSTANCE] on [IDSETREGISTERSMARTQUERYINSTANCE].[IDSETREGISTERID] = [IDSETREGISTER].[ID]
  left join dbo.[SMARTQUERYINSTANCE] on [SMARTQUERYINSTANCE].[ID] = [IDSETREGISTERSMARTQUERYINSTANCE].[SMARTQUERYINSTANCEID]
  left join dbo.[MKTSEGMENTLIST] as [MKTSEGMENTLIST] on [MKTSEGMENTLIST].[STANDARDIDSETID] = [IDSETREGISTER].[ID] or [MKTSEGMENTLIST].[DUPLICATEIDSETID] = [IDSETREGISTER].[ID]
  left join dbo.[QUERYVIEWCATALOG] on [QUERYVIEWCATALOG].[ID] = [ADHOCQUERY].[QUERYVIEWCATALOGID]
  where [IDSETREGISTER].[ID] = @ID
  and [IDSETREGISTER].[STATIC] = 1
  and [IDSETREGISTER].[ISSYSTEM] = 0
  and [IDSETREGISTER].[ACTIVE] = 1
  and ([QUERYVIEWCATALOG].[ID] is null or [QUERYVIEWCATALOG].[ROOTOBJECT] = 1);

  return 0;