USP_SPONSORSHIP_INACTIVATE

CREATE procedure dbo.USP_SPONSORSHIP_INACTIVATE (
    @SPONSORSHIPID uniqueidentifier,
    @ENDDATE date = null,
    @UPDATEAVAILABILITY bit = 0,
    @CHANGEAGENTID uniqueidentifier = null
)
as
begin
    set nocount on;

    declare @CURRENTDATE datetime;
    set @CURRENTDATE = getdate();

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

    declare @SPONSORSHIPOPPORTUNITYID uniqueidentifier

    select @SPONSORSHIPOPPORTUNITYID = SPONSORSHIPOPPORTUNITYID
    from dbo.SPONSORSHIP
    where ID = @SPONSORSHIPID;

    begin try
        -- inactivate the sponsorship

        update dbo.SPONSORSHIP
        set STATUSCODE = 2,
            ENDDATE = @ENDDATE,
            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        where ID = @SPONSORSHIPID;

        -- return the opportunity to available if cancelling

        if @UPDATEAVAILABILITY = 1
            exec dbo.USP_SPONSORSHIPOPPORTUNITY_SPONSORSHIPINACTIVATED
                @SPONSORSHIPOPPORTUNITYID,
                @CHANGEAGENTID,
        1
    end try
    begin catch
      exec dbo.USP_RAISE_ERROR;
      return 1;
    end catch

    return 0
end