USP_DATAFORMTEMPLATE_VIEW_MKTLISTLAYOUT

The load procedure used by the view dataform template "List Layout 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.
@NAME	nvarchar(50)	INOUT	Name
@DESCRIPTION	nvarchar(100)	INOUT	Description
@LAYOUTLISTSEGMENTS	xml	INOUT	List segments imported using this layout
@LAYOUTFIELDS	xml	INOUT	Field list
@ADDNEWCODETABLEENTRIES	bit	INOUT

Definition

Copy



CREATE procedure dbo.[USP_DATAFORMTEMPLATE_VIEW_MKTLISTLAYOUT]
(
  @ID uniqueidentifier,
  @DATALOADED bit = 0 output,
  @NAME nvarchar(50) = null output,
  @DESCRIPTION nvarchar(100) = null output,
  @LAYOUTLISTSEGMENTS xml = null output,
  @LAYOUTFIELDS xml = null output,
  @ADDNEWCODETABLEENTRIES bit = null output
)
as
  set nocount on;

  set @DATALOADED = 0;

  select
    @DATALOADED = 1,
    @NAME = [NAME],
    @DESCRIPTION = [DESCRIPTION],
    @ADDNEWCODETABLEENTRIES = [ADDNEWCODETABLEENTRIES]
  from dbo.[MKTLISTLAYOUT]
  where [ID] = @ID;

  if @DATALOADED = 1
    begin
      set @LAYOUTFIELDS = (
        select
          [FRIENDLYNAME],
          [FIELDNAME],
          [SOURCENAME] as [LAYOUTNAME]
        from [MKTLISTLAYOUTFIELD]
        where [LISTLAYOUTID] = @ID
        for xml raw('ITEM'), type, elements, root('LAYOUTFIELDS'), binary base64);

      set @LAYOUTLISTSEGMENTS = (
        select
          (case when [MKTSEGMENTLISTHISTORICAL].[ID] is null then [MKTSEGMENT].[NAME] else '    ' + [MKTSEGMENTLISTHISTORICAL].[SEGMENTNAME] end) as [NAME],
          (case when [MKTSEGMENTLISTHISTORICAL].[ID] is null then [MKTSEGMENT].[DESCRIPTION] else [MKTSEGMENTLISTHISTORICAL].[SEGMENTDESCRIPTION] end) as [DESCRIPTION],
          [STATUS],
          (case when [MKTSEGMENTLISTHISTORICAL].[ID] is null then [MKTSEGMENTLIST].[DATECHANGED] else [MKTSEGMENTLISTHISTORICAL].[DATEADDED] end) as [DATEADDED]
        from [MKTSEGMENTLIST]
        inner join dbo.[MKTSEGMENT] on [MKTSEGMENT].[ID] = [MKTSEGMENTLIST].[SEGMENTID]
        left join dbo.[MKTSEGMENTLISTHISTORICAL] on [MKTSEGMENTLISTHISTORICAL].[ID] = [MKTSEGMENTLIST].[ID]
        where [LISTLAYOUTID] = @ID
        for xml raw('ITEM'), type, elements, root('LAYOUTLISTSEGMENTS'), binary base64);
    end

  return 0;