USP_GLOBALCHANGE_SPONSORSHIPOPPORTUNITYUNRESERVE

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

  declare @SUCCESSCOUNT int
    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate();
    set @NUMBERADDED = 0;
    set @NUMBEREDITED = 0;
    set @NUMBERDELETED = 0; 
  set @SUCCESSCOUNT = 0;

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

    begin try

    declare @KEYID uniqueidentifier;

    declare @TempTbl table(ID uniqueidentifier)

    insert into @TempTbl(ID)
    select SPR.ID
    from dbo.SPONSORSHIPOPPORTUNITYRESERVEPROCESS SPR 
    inner join dbo.SPONSORSHIPOPPORTUNITYRESERVATIONSTATUS SPS on SPS.OPPORTUNITYRESERVATIONKEYID = SPR.ID
    where SPS.STATUSCODE = 1 and SPR.ENDDATE <= @CURRENTDATE

    declare UNRESERVE_CURSOR cursor local fast_forward for
      select ID from @TempTbl

    open UNRESERVE_CURSOR;
          fetch next from UNRESERVE_CURSOR into
        @KEYID;

    while (@@FETCH_STATUS = 0)
        begin             

      exec dbo.USP_SPONSORSHIPOPPORTUNITY_UNRESERVE @KEYID, @CHANGEAGENTID, @SUCCESSCOUNT output;      

            set @NUMBEREDITED = @NUMBEREDITED + @SUCCESSCOUNT

            fetch next from UNRESERVE_CURSOR into
                @KEYID;                
        end       
        close UNRESERVE_CURSOR;
        deallocate UNRESERVE_CURSOR;     

        return 0; 
    end try

    begin catch
    close UNRESERVE_CURSOR;
        deallocate UNRESERVE_CURSOR;
        exec dbo.USP_RAISE_ERROR;
        return 1;
    end catch