USP_MKTSPONSORSHIPMAILINGPROCESS_ROLLBACK

create procedure dbo.[USP_MKTSPONSORSHIPMAILINGPROCESS_ROLLBACK]
(
  @MKTSPONSORSHIPMAILINGPROCESSSTATUSID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null
)
as
  set nocount on;

  declare @RECORDSOURCEID uniqueidentifier;
  declare @SQL as nvarchar(max);
  declare @CONTEXTCACHE varbinary(128);
  declare @MKTSPONSORSHIPMAILINGPROCESSSEGMENTATIONID uniqueidentifier;
  declare @SEGMENTATIONID uniqueidentifier;

  begin try
    if @CHANGEAGENTID is null  
      exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENT] @CHANGEAGENTID output;

    -- remove the mailing, if one was created


    select
      @MKTSPONSORSHIPMAILINGPROCESSSEGMENTATIONID = [ID],
      @SEGMENTATIONID = [SEGMENTATIONID]
    from dbo.[MKTSPONSORSHIPMAILINGPROCESSSEGMENTATION]
    where [SPONSORSHIPMAILINGPROCESSSTATUSID] = @MKTSPONSORSHIPMAILINGPROCESSSTATUSID;

    if @MKTSPONSORSHIPMAILINGPROCESSSEGMENTATIONID is not null
      begin
        exec dbo.[USP_MKTSPONSORSHIPMAILINGPROCESSSEGMENTATION_DELETEBYID_WITHCHANGEAGENTID] @MKTSPONSORSHIPMAILINGPROCESSSEGMENTATIONID, @CHANGEAGENTID;

        if @SEGMENTATIONID is not null
          exec dbo.[USP_MKTSEGMENTATION_DELETE] @SEGMENTATIONID, @CHANGEAGENTID;
      end
  end try

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

  return 0;