USP_DATAFORMTEMPLATE_VIEW_MKTSELECTION_BYQUERYID

The load procedure used by the view dataform template "Marketing Selection View Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@SELECTIONID	uniqueidentifier	INOUT	Selection ID
@NAME	nvarchar(300)	INOUT	Name
@DESCRIPTION	nvarchar(1024)	INOUT	Description
@RECORDTYPEID	uniqueidentifier	INOUT	Record type ID
@RECORDTYPENAME	nvarchar(50)	INOUT	Record type
@DBOBJECTNAME	nvarchar(128)	INOUT	Database object name
@OBJECTTYPE	tinyint	INOUT	Object type
@FORMAT	nvarchar(7)	INOUT	Format
@NUMROWS	int	INOUT	Number of records
@USEINQUERYDESIGNER	bit	INOUT	Use in query designer
@ADDEDBYUSER	nvarchar(128)	INOUT	Added by
@DATEADDED	datetime	INOUT	Date added
@CHANGEDBYUSER	nvarchar(128)	INOUT	Changed by
@DATECHANGED	datetime	INOUT	Date changed
@STATIC	bit	INOUT	Static ID set
@QUERYCATEGORYCODEID	uniqueidentifier	INOUT
@QUERYCATEGORYCODE	nvarchar(100)	INOUT
@QUERYNAME	nvarchar(100)	INOUT

Definition

Copy


CREATE procedure dbo.[USP_DATAFORMTEMPLATE_VIEW_MKTSELECTION_BYQUERYID]
(
  @ID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @SELECTIONID uniqueidentifier = null output,
  @NAME nvarchar(300) = null output,
  @DESCRIPTION nvarchar(1024) = null output,
  @RECORDTYPEID uniqueidentifier = null output,
  @RECORDTYPENAME nvarchar(50) = null output,
  @DBOBJECTNAME nvarchar(128) = null output,
  @OBJECTTYPE tinyint = null output,
  @FORMAT nvarchar(7) = null output,
  @NUMROWS int = null output,
  @USEINQUERYDESIGNER bit = null output,
  @ADDEDBYUSER nvarchar(128) = null output,
  @DATEADDED datetime = null output,
  @CHANGEDBYUSER nvarchar(128) = null output,
  @DATECHANGED datetime = null output,
  @STATIC bit = null output,
  @QUERYCATEGORYCODEID uniqueidentifier = null output,
  @QUERYCATEGORYCODE nvarchar(100) = null output,
  @QUERYNAME nvarchar(100) = null output
)
as
  set nocount on;

  set @DATALOADED = 0;

  select 
    @DATALOADED = 1,
    @SELECTIONID = [IDSETREGISTER].[ID],
    @NAME = [IDSETREGISTER].[NAME],
    @DESCRIPTION = [IDSETREGISTER].[DESCRIPTION],
    @RECORDTYPEID = [IDSETREGISTER].[RECORDTYPEID],
    @RECORDTYPENAME = [RECORDTYPE].[NAME],
    @DBOBJECTNAME = [IDSETREGISTER].[DBOBJECTNAME],
    @OBJECTTYPE = [IDSETREGISTER].[OBJECTTYPE],
    @FORMAT = (case [IDSETREGISTER].[STATIC] when 0 then 'Dynamic' else 'Static' end),
    @NUMROWS = [IDSETREGISTER].[NUMROWS],
    @USEINQUERYDESIGNER = [IDSETREGISTER].[USEINQUERYDESIGNER],
    @ADDEDBYUSER = [ADDEDBYUSER].[USERNAME],
    @DATEADDED = [IDSETREGISTER].[DATEADDED],
    @CHANGEDBYUSER = [CHANGEDBYUSER].[USERNAME],
    @DATECHANGED = [IDSETREGISTER].[DATECHANGED],
    @STATIC = [IDSETREGISTER].[STATIC],
    @QUERYCATEGORYCODEID = [QUERYCATEGORYCODE].[ID],
    @QUERYCATEGORYCODE = [QUERYCATEGORYCODE].[DESCRIPTION],
    @QUERYNAME = isnull([ADHOCQUERY].[NAME], [SMARTQUERYINSTANCE].[NAME])
  from dbo.[IDSETREGISTER]
  left outer join dbo.[IDSETREGISTERADHOCQUERY] on [IDSETREGISTERADHOCQUERY].[IDSETREGISTERID] = IDSETREGISTER.[ID]
  left outer join dbo.[IDSETREGISTERSMARTQUERYINSTANCE] on [IDSETREGISTERSMARTQUERYINSTANCE].[IDSETREGISTERID] = IDSETREGISTER.[ID]
  left outer join dbo.[ADHOCQUERY] on [ADHOCQUERY].[ID] = [IDSETREGISTERADHOCQUERY].[ADHOCQUERYID]
  left outer join dbo.[SMARTQUERYINSTANCE] on [SMARTQUERYINSTANCE].[ID] = [IDSETREGISTERSMARTQUERYINSTANCE].[SMARTQUERYINSTANCEID]
  left outer join dbo.[QUERYCATEGORYCODE] on [QUERYCATEGORYCODE].[ID] = isnull([ADHOCQUERY].[QUERYCATEGORYCODEID], [SMARTQUERYINSTANCE].[QUERYCATEGORYCODEID])
  left outer join dbo.[RECORDTYPE] on [RECORDTYPE].[ID] = [IDSETREGISTER].[RECORDTYPEID]
  left outer join dbo.[CHANGEAGENT] as [ADDEDBYUSER] on [ADDEDBYUSER].[ID] = [IDSETREGISTER].[ADDEDBYID]
  left outer join dbo.[CHANGEAGENT] as [CHANGEDBYUSER] on [CHANGEDBYUSER].[ID] = [IDSETREGISTER].[CHANGEDBYID]
  where coalesce([IDSETREGISTERADHOCQUERY].[ADHOCQUERYID], [IDSETREGISTERSMARTQUERYINSTANCE].[SMARTQUERYINSTANCEID]) = @ID;

  return 0;