USP_DATAFORMTEMPLATE_EDITLOAD_MKTSEGMENTATIONFINDERNUMBER

The load procedure used by the edit dataform template "Marketing Effort Finder Number Edit Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter used to load the fields defined on the form.
@MAXVALUE	bigint	INOUT	Maximum value
@CHECKDIGIT	bit	INOUT	Check digit
@MIN	bigint	INOUT	Range
@MAX	bigint	INOUT	Quantity
@VENDORID	uniqueidentifier	INOUT	Reserved for
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@SEGMENTATIONID	uniqueidentifier	INOUT	Segmentation ID
@ASSIGNED	bigint	INOUT	Assigned
@TSLONG	bigint	INOUT	Output parameter indicating the TSLONG value of the record being edited. This is used to manage multi-user concurrency issues when multiple users access the same record.

Definition

Copy



CREATE procedure dbo.[USP_DATAFORMTEMPLATE_EDITLOAD_MKTSEGMENTATIONFINDERNUMBER]
(
  @ID uniqueidentifier,
  @MAXVALUE bigint = null output,
  @CHECKDIGIT bit = null output,  
  @MIN bigint = null output,
  @MAX bigint = null output,
  @VENDORID uniqueidentifier = null output,
  @DATALOADED bit = 0 output,
  @SEGMENTATIONID uniqueidentifier = null output,
  @ASSIGNED bigint = null output,
  @TSLONG bigint = 0 output
)
as
  set nocount on;

  declare @MAILINGTYPECODE tinyint;
  declare @DATATABLE nvarchar(128);
  declare @SQL nvarchar(max);
  declare @MINTEMP bigint;
  declare @MAXTEMP bigint;

  set @DATALOADED = 0;
  set @TSLONG = 0;

  select
    @MAXVALUE = convert(bigint, replicate('9', [FIXEDWIDTH])),
    @CHECKDIGIT = [CHECKDIGIT]
  from dbo.[MKTFINDERNUMBER];

  select
    @DATALOADED = 1,
    @MIN = [MIN],
    @MAX = [MAX],
    @VENDORID = isnull([VENDORID], '00000000-0000-0000-0000-000000000001'),
    @SEGMENTATIONID = [SEGMENTATIONID],
    @ASSIGNED = 0,
    @TSLONG = [TSLONG]
  from dbo.[MKTSEGMENTATIONFINDERNUMBER]
  where [ID] = @ID;

  if @DATALOADED = 1
    begin
      select @MAILINGTYPECODE = [MAILINGTYPECODE] from dbo.[MKTSEGMENTATION] where [ID] = @SEGMENTATIONID;
      set @DATATABLE = dbo.[UFN_MKTSEGMENTATIONACTIVATE_MAKETABLENAME](@SEGMENTATIONID);

      if exists(select top 1 1 from [INFORMATION_SCHEMA].[TABLES] where [TABLE_SCHEMA] = 'dbo' and [TABLE_NAME] = @DATATABLE)
        begin
          if @MAILINGTYPECODE = 2 -- memberships

            set @SQL = 'select @ASSIGNED = count(distinct [FINDERNUMBER]) from dbo.[' + @DATATABLE + '] where [FINDERNUMBER] > 0 and [FINDERNUMBER] between @MIN and @MAX';
          else
            set @SQL = 'select @ASSIGNED = count([FINDERNUMBER]) from dbo.[' + @DATATABLE + '] where [FINDERNUMBER] > 0 and [FINDERNUMBER] between @MIN and @MAX';

          set @MINTEMP = @MIN;
          set @MAXTEMP = @MAX;

          if @CHECKDIGIT = 1
            begin
              set @MINTEMP = @MINTEMP * 10;
              set @MAXTEMP = (@MAXTEMP * 10) + 9;
            end

          exec sp_executesql @SQL, N'@ASSIGNED bigint output, @MIN bigint, @MAX bigint', @ASSIGNED = @ASSIGNED output, @MIN = @MINTEMP, @MAX = @MAXTEMP;
        end
    end

  return 0;