USP_GLOBALCHANGE_CANCELFIXEDTERMSPONSORSHIPS

CREATE procedure dbo.USP_GLOBALCHANGE_CANCELFIXEDTERMSPONSORSHIPS
(
    @CHANGEAGENTID uniqueidentifier = null,
    @ASOF as datetime = null,
    @NUMBERADDED int = 0 output,
    @NUMBEREDITED int = 0 output,
    @NUMBERDELETED int = 0 output
)
as            
    set nocount off;

    declare @CURRENTDATETIME datetime
    declare @CURRENTDATE date
    set @CURRENTDATETIME = getdate();
    set @CURRENTDATE = @CURRENTDATETIME;
    set @NUMBERADDED = 0;
    set @NUMBEREDITED = 0;
    set @NUMBERDELETED = 0; 

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

    begin try
        -- do the global change operation here, and set @NUMBERADDED, @NUMBEREDITED, and/or @NUMBERDELETED as appropriate

        declare @SPONSORSHIPID uniqueidentifier;
        declare @PLANNEDENDDATE datetime;
        declare @EXPIRATIONREASONID uniqueidentifier;
        declare @TempTbl table(ID uniqueidentifier,
                               PLANNEDENDDATE datetime,
                               EXPIRATIONREASONID uniqueidentifier);

        insert into @TempTbl(ID,PLANNEDENDDATE,EXPIRATIONREASONID) 
        select ID,PLANNEDENDDATE,EXPIRATIONREASONID
        from dbo.SPONSORSHIP
        where PLANNEDENDDATE is not null
        and PLANNEDENDDATE < @CURRENTDATE;

        declare SPONSORSHIP_CURSOR cursor local fast_forward for
        select ID,PLANNEDENDDATE,EXPIRATIONREASONID from @TempTbl

        open SPONSORSHIP_CURSOR;
        fetch next from SPONSORSHIP_CURSOR into @SPONSORSHIPID,@PLANNEDENDDATE,@EXPIRATIONREASONID;

    while (@@FETCH_STATUS = 0)
        begin
           exec dbo.USP_SPONSORSHIP_CLOSECOMMITMENT
             @SPONSORSHIPID,
             @PLANNEDENDDATE,
             2,
             @EXPIRATIONREASONID

            update dbo.SPONSORSHIP
            set PLANNEDENDDATE = null,
                EXPIRATIONREASONID = null,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATETIME
            where ID = @SPONSORSHIPID;

            set @NUMBEREDITED = @NUMBEREDITED + 1

            fetch next from SPONSORSHIP_CURSOR into @SPONSORSHIPID,@PLANNEDENDDATE,@EXPIRATIONREASONID
        end
        close SPONSORSHIP_CURSOR;
        deallocate SPONSORSHIP_CURSOR;
    end try

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