USP_MKTSEGMENTSREFRESH_ADDREFRESHSTATUS

Adds a segment refresh process status record so you can view the refresh history for individual segments.

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	INOUT
@SEGMENTID	uniqueidentifier	IN
@PARENTBUSINESSPROCESSSTATUSTABLENAME	nvarchar(128)	IN
@PARENTBUSINESSPROCESSPARAMETERSETID	uniqueidentifier	IN

Definition

Copy



CREATE procedure dbo.[USP_MKTSEGMENTSREFRESH_ADDREFRESHSTATUS]
(
  @ID uniqueidentifier output,
  @SEGMENTID uniqueidentifier,
  @PARENTBUSINESSPROCESSSTATUSTABLENAME nvarchar(128),
  @PARENTBUSINESSPROCESSPARAMETERSETID uniqueidentifier
)
as
  set nocount on;

  declare @STARTEDBYUSERID uniqueidentifier;
  declare @SERVERNAME nvarchar(255);
  declare @CHANGEAGENTID uniqueidentifier;
  declare @SEGMENTREFRESHPROCESSID uniqueidentifier;
  declare @SQL nvarchar(max);

  begin try
    if @ID is null set @ID = newid();

    select
      @SEGMENTREFRESHPROCESSID = [ID]
    from dbo.[MKTSEGMENTREFRESHPROCESS]
    where [SEGMENTID] = @SEGMENTID;

    set @SQL = 
      'select top 1' + char(13) +
      '  @STARTEDBYUSERID = [BUSINESSPROCESSSTATUS].[STARTEDBYUSERID],' + char(13) +
      '  @SERVERNAME = [BUSINESSPROCESSSTATUS].[SERVERNAME],' + char(13) +
      '  @CHANGEAGENTID = [BUSINESSPROCESSSTATUS].[ADDEDBYID]' + char(13) +
      'from dbo.[' + @PARENTBUSINESSPROCESSSTATUSTABLENAME + ']' + char(13) +
      'inner join dbo.[BUSINESSPROCESSSTATUS] on [BUSINESSPROCESSSTATUS].[ID] = [' + @PARENTBUSINESSPROCESSSTATUSTABLENAME + '].[ID]' + char(13) +
      'where [' + @PARENTBUSINESSPROCESSSTATUSTABLENAME + '].[PARAMETERSETID] = @PARENTBUSINESSPROCESSPARAMETERSETID' + char(13) +
      'order by [' + @PARENTBUSINESSPROCESSSTATUSTABLENAME + '].[DATEADDED] desc;';

    exec sp_executesql @SQL, 
                       N'@STARTEDBYUSERID uniqueidentifier output, @SERVERNAME nvarchar(510) output, @CHANGEAGENTID uniqueidentifier output, @PARENTBUSINESSPROCESSPARAMETERSETID uniqueidentifier', 
                       @STARTEDBYUSERID = @STARTEDBYUSERID output, @SERVERNAME = @SERVERNAME output, @CHANGEAGENTID = @CHANGEAGENTID output, @PARENTBUSINESSPROCESSPARAMETERSETID = @PARENTBUSINESSPROCESSPARAMETERSETID;

    --Add a new status for the individual segment...

    exec dbo.[USP_BUSINESSPROCESSSTATUS_ADDPROCESS]
      @ID,
      'E27CC00C-2CBC-44C6-9CE6-398B9198A183',
      @SEGMENTREFRESHPROCESSID,
      'MKTSEGMENTREFRESHPROCESSSTATUS',
      @STARTEDBYUSERID,
      @SERVERNAME,
      @CHANGEAGENTID;
  end try

  begin catch
    exec dbo.[USP_RAISE_ERROR];
    return 1;
  end catch

  return 0;