USP_SPONSORSHIPOPPORTUNITY_UNRESERVE

CREATE procedure dbo.USP_SPONSORSHIPOPPORTUNITY_UNRESERVE
(
    @KEYID uniqueidentifier, 
    @CHANGEAGENTID uniqueidentifier = null,
  @SUCCESSCOUNT int = 0 output,
    @EXCEPTIONCOUNT 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 @SUCCESSCOUNT = 0
  set @EXCEPTIONCOUNT = 0

  begin try  
  -- Updating the children by deleting the reservationkey

  declare @SUCCESS int;   

  update dbo.SPONSORSHIPOPPORTUNITY   
    set RESERVATIONKEYID = null, AVAILABILITYCODE = 0, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CURRENTDATE
    where RESERVATIONKEYID = @KEYID    
    set @SUCCESSCOUNT = @@ROWCOUNT

  -- Updating the key to Expired status

  update dbo.SPONSORSHIPOPPORTUNITYRESERVATIONSTATUS          
    set STATUSCODE = 0, CHANGEDBYID = @CHANGEAGENTID, DATECHANGED = @CURRENTDATE
    where OPPORTUNITYRESERVATIONKEYID = @KEYID      

    end try
    begin catch
      exec dbo.USP_RAISE_ERROR;
    set @EXCEPTIONCOUNT = @EXCEPTIONCOUNT + 1
      return 1;
    end catch

    return 0;
end