USP_DATAFORMTEMPLATE_VIEW_ADHOCQUERY_SOURCEVIEW

The load procedure used by the view dataform template "AdHoc Query Source View Form"

Parameters

Parameter	Parameter Type	Mode	Description
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@SOURCEVIEWS_COMMON	xml	INOUT	SOURCEVIEWS_COMMON
@SOURCEVIEWS	xml	INOUT	Select a Query Type

Definition

Copy


CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_ADHOCQUERY_SOURCEVIEW
(
  @CURRENTAPPUSERID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @SOURCEVIEWS_COMMON xml = null output,
  @SOURCEVIEWS xml = null output
)
as
  set nocount on;

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

  declare @QUERY_TYPES table
  (
      ID uniqueidentifier,
      OBJECTNAME nvarchar(128),     
      DISPLAYNAME nvarchar(255),
      RECORDTYPEID uniqueidentifier,
      RECORDTYPE nvarchar(50),
      OBJECTTYPE tinyint,
      [DESCRIPTION] nvarchar(max),
      USEINREPORTMODELGENERATOR bit
  )
  insert into @QUERY_TYPES
  exec dbo.[USP_QUERYVIEWCATALOG_GETROOTLIST] @CURRENTAPPUSERID

  declare @VISIBLE_QUERY_TYPES table
  (
      ID uniqueidentifier,    
      DISPLAYNAME nvarchar(255),
      RECORDTYPE nvarchar(50),
      [DESCRIPTION] nvarchar(max)
  )

  insert into @VISIBLE_QUERY_TYPES
  select
      QUERY_TYPES.ID,
      QUERY_TYPES.DISPLAYNAME,
      QUERY_TYPES.RECORDTYPE,
      QUERY_TYPES.DESCRIPTION 
  from @QUERY_TYPES QUERY_TYPES
      inner join dbo.V_INSTALLED_QUERYVIEWCATALOG on QUERY_TYPES.ID = V_INSTALLED_QUERYVIEWCATALOG.ID
      left outer join (select [ID] from dbo.[RECORDTYPE] where [NAME] like '% Segment Member Export') marketingefforts on QUERY_TYPES.RECORDTYPEID = marketingefforts.ID
  where 
    -- hide marketing effort export record types
    --QUERY_TYPES.RECORDTYPEID not in (select [ID] from dbo.[RECORDTYPE] where [NAME] like '% Segment Member Export')
    marketingefforts.ID is null

  select
      @SOURCEVIEWS = (
        select
            VISIBLE_QUERY_TYPES.ID,
            VISIBLE_QUERY_TYPES.DISPLAYNAME as NAME,
            VISIBLE_QUERY_TYPES.RECORDTYPE as GROUPTYPE,
            VISIBLE_QUERY_TYPES.DESCRIPTION     
        from @VISIBLE_QUERY_TYPES VISIBLE_QUERY_TYPES
        order by GROUPTYPE, NAME
        for xml raw('ITEM'),type,elements,root('SOURCEVIEWS'),binary base64
      ),
      @SOURCEVIEWS_COMMON = (
        select top(5)
            VISIBLE_QUERY_TYPES.ID,
            VISIBLE_QUERY_TYPES.DISPLAYNAME as NAME,
            VISIBLE_QUERY_TYPES.RECORDTYPE as GROUPTYPE,
            VISIBLE_QUERY_TYPES.DESCRIPTION
        from @VISIBLE_QUERY_TYPES VISIBLE_QUERY_TYPES
            inner join dbo.ADHOCQUERY on VISIBLE_QUERY_TYPES.ID = ADHOCQUERY.QUERYVIEWCATALOGID
        group by
            VISIBLE_QUERY_TYPES.ID,
            VISIBLE_QUERY_TYPES.DISPLAYNAME,
            VISIBLE_QUERY_TYPES.RECORDTYPE,
            VISIBLE_QUERY_TYPES.DESCRIPTION
        order by
            count(ADHOCQUERY.ID) desc
        for xml raw('ITEM'),type,elements,root('SOURCEVIEWS_COMMON'),binary base64
      )

  return 0;