USP_MKTSEGMENTATIONACTIVATE_CACHEPACKAGES

CREATE procedure dbo.[USP_MKTSEGMENTATIONACTIVATE_CACHEPACKAGES]
(
  @SEGMENTATIONID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier = null
)
as
  set nocount on;

  declare @PACKAGEID uniqueidentifier;
  declare @CURRENTDATE datetime;

  begin try
    /* Only save this info if this is the first activation */
    if (select [ACTIVE] from dbo.[MKTSEGMENTATION] where [ID] = @SEGMENTATIONID) = 0
      begin
        if @CHANGEAGENTID is null
          exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENT] @CHANGEAGENTID output;

        set @CURRENTDATE = getdate();

        declare PACKAGES cursor local fast_forward for
          select [PACKAGEID] from dbo.[MKTSEGMENTATIONPACKAGE] where [SEGMENTATIONID] = @SEGMENTATIONID;

        open PACKAGES;
        fetch next from PACKAGES into @PACKAGEID;

        while (@@FETCH_STATUS = 0)
          begin
            exec dbo.[USP_MKTSEGMENTATIONACTIVATE_CACHEPACKAGE] @SEGMENTATIONID, @PACKAGEID, @CHANGEAGENTID, @CURRENTDATE;

            fetch next from PACKAGES into @PACKAGEID;
          end

        close PACKAGES;
        deallocate PACKAGES;
      end
  end try

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

  return 0;