USP_SPONSORSHIPOPPORTUNITY_SPONSORSHIPINACTIVATED

Updates a sponsorship opportunity's availability after one if its sponsorships has been inactivated.

Parameters

Parameter	Parameter Type	Mode
@SPONSORSHIPOPPORTUNITYID	uniqueidentifier	IN
@CHANGEAGENTID	uniqueidentifier	IN
@DECREMENTSPONSORCOUNT	bit	IN

Definition

Copy



CREATE procedure dbo.USP_SPONSORSHIPOPPORTUNITY_SPONSORSHIPINACTIVATED (
    @SPONSORSHIPOPPORTUNITYID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
  @DECREMENTSPONSORCOUNT bit = 1
)
as
begin
    declare @CURRENTDATE datetime;
    set @CURRENTDATE = getdate();

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

    begin try
        update dbo.SPONSORSHIPOPPORTUNITY
        set AVAILABILITYCODE = (select case SPONSORSHIPLOCATION.STATUSCODE when 0 then 0 else 2 end
                            from dbo.SPONSORSHIPLOCATION
                            where SPONSORSHIPLOCATION.ID = SPONSORSHIPOPPORTUNITY.SPONSORSHIPLOCATIONID),
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
        where ID = @SPONSORSHIPOPPORTUNITYID
    and AVAILABILITYCODE <> 1;

    if @DECREMENTSPONSORCOUNT = 1
      update dbo.SPONSORSHIPOPPORTUNITYLOCK
      set SPONSORCOUNT = SPONSORCOUNT - 1,
          CHANGEDBYID = @CHANGEAGENTID,
          DATECHANGED = @CURRENTDATE
      where ID = @SPONSORSHIPOPPORTUNITYID and SPONSORCOUNT>0;
    end try
    begin catch
      exec dbo.USP_RAISE_ERROR;
      return 1;
    end catch

    return 0
end