USP_SPONSORSHIP_COMPLETETRANSFERS

CREATE procedure dbo.USP_SPONSORSHIP_COMPLETETRANSFERS (
    @OLDERTHANDATE datetime,
    @CHANGEAGENTID uniqueidentifier = null,
  @IDSETREGISTERID uniqueidentifier = null,
    @NUMBERADDED int = 0 output,
    @NUMBEREDITED int = 0 output,
    @NUMBERDELETED int = 0 output
)
as
begin
    set nocount on;

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

    if @CHANGEAGENTID is null  
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

    set @NUMBERADDED = 0
    set @NUMBEREDITED = 0
    set @NUMBERDELETED = 0

    declare @FROMSPONSORSHIPID uniqueidentifier
    declare @TOSPONSORSHIPID uniqueidentifier

    declare @t table(ID uniqueidentifier)
    insert into @t(ID) select ID from dbo.UFN_IDSETREADER_GETRESULTS(@IDSETREGISTERID)

    declare SPONSORSHIP_CURSOR cursor local fast_forward for
        select CONTEXTSPONSORSHIPID,
               TARGETSPONSORSHIPID
        from dbo.SPONSORSHIPTRANSACTION PENDING 
      left join @t IDSET on PENDING.ID = IDSET.ID
        where ACTIONCODE = 6
        and TRANSACTIONDATE <= @OLDERTHANDATE
        and not exists(select 'x'
                       from dbo.SPONSORSHIPTRANSACTION COMPLETED
                       where COMPLETED.SPONSORSHIPCOMMITMENTID = PENDING.SPONSORSHIPCOMMITMENTID
                       and COMPLETED.TRANSACTIONSEQUENCE = PENDING.TRANSACTIONSEQUENCE + 1)
    and (@IDSETREGISTERID is null or PENDING.ID in(select ID from @t));                                                          

    open SPONSORSHIP_CURSOR;
    begin try
        fetch next from SPONSORSHIP_CURSOR into
            @FROMSPONSORSHIPID,
            @TOSPONSORSHIPID;

        while (@@FETCH_STATUS = 0)
        begin
            exec dbo.USP_SPONSORSHIP_COMPLETETRANSFER
                @TOSPONSORSHIPID,
                @FROMSPONSORSHIPID,
                1,
                @CHANGEAGENTID

            set @NUMBEREDITED = @NUMBEREDITED + 1

            fetch next from SPONSORSHIP_CURSOR into
                @FROMSPONSORSHIPID,
                @TOSPONSORSHIPID;
        end
        close SPONSORSHIP_CURSOR;
        deallocate SPONSORSHIP_CURSOR;
    end try
    begin catch
      close SPONSORSHIP_CURSOR;
      deallocate SPONSORSHIP_CURSOR;
      exec dbo.USP_RAISE_ERROR;
      return 1;
    end catch

    return 0;
end