USP_SPONSORSHIPOPPORTUNITY_EDITSAVE_PROJECT_2

The save procedure used by the edit dataform template "Sponsorship Opportunity Project Edit Form".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter indicating the ID of the record being edited.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@SPONSORSHIPLOCATIONID	uniqueidentifier	IN	Location
@PROJECTNAME	nvarchar(100)	IN	Name
@DESCRIPTION	nvarchar(255)	IN	Description
@SPONSORSHIPOPPORTUNITYGROUPID	uniqueidentifier	IN	Project group
@GOAL	money	IN	Amount
@SPONSORGOAL	int	IN	Sponsors
@DESIGNATIONID	uniqueidentifier	IN	Designation
@SPROPPPROJECTCATEGORYCODE	uniqueidentifier	IN	Category

Definition

Copy


CREATE procedure dbo.USP_SPONSORSHIPOPPORTUNITY_EDITSAVE_PROJECT_2
(
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null,
    @SPONSORSHIPLOCATIONID uniqueidentifier,
    @PROJECTNAME nvarchar(100),
    @DESCRIPTION nvarchar(255),
    @SPONSORSHIPOPPORTUNITYGROUPID uniqueidentifier,
    @GOAL money,
    @SPONSORGOAL int,
  @DESIGNATIONID uniqueidentifier,
  @SPROPPPROJECTCATEGORYCODE uniqueidentifier
)
as

set nocount on;

if @ID is null
    set @ID = newid()

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

declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()

declare @AVAILABILITYCODE tinyint

select @AVAILABILITYCODE = case STATUSCODE when 0 then 0 else 2 end
from dbo.SPONSORSHIPLOCATION
where ID = @SPONSORSHIPLOCATIONID

--validate location change
declare @OLDSPONSORSHIPLOCATIONID uniqueidentifier;
select @OLDSPONSORSHIPLOCATIONID = SPONSORSHIPLOCATIONID
from dbo.SPONSORSHIPOPPORTUNITY 
where ID = @ID;

if @OLDSPONSORSHIPLOCATIONID <> @SPONSORSHIPLOCATIONID
  begin
    declare @SPONSORSPEROPPORTUNITY int;
    select @SPONSORSPEROPPORTUNITY = dbo.UFN_SPONSORSHIPOPPORTUNITY_SPONSORSPEROPPORTUNITY(@SPONSORSHIPOPPORTUNITYGROUPID, @SPONSORSHIPLOCATIONID);


    if @SPONSORSPEROPPORTUNITY > 0 and dbo.UFN_SPONSORSHIPOPPORTUNITY_ACTIVESPONSORSHIPS(@ID) > @SPONSORSPEROPPORTUNITY
      raiserror('BBERR_INVALIDLOCATIONCHANGE',13,1)
  end


begin try

  update
     dbo.SPONSORSHIPOPPORTUNITY
  set 
       SPONSORSHIPOPPORTUNITYGROUPID = @SPONSORSHIPOPPORTUNITYGROUPID,
       SPONSORSHIPLOCATIONID = @SPONSORSHIPLOCATIONID,
       AVAILABILITYCODE = case when @AVAILABILITYCODE = 2 then 2 when AVAILABILITYCODE = 1 then 1 else dbo.UFN_SPONSORSHIPOPPORTUNITY_CALCAVAILABILITY(ID) end,
       CHANGEDBYID = @CHANGEAGENTID,
       DATECHANGED = @CURRENTDATE
  where
     ID = @ID;

  update
     dbo.SPONSORSHIPOPPORTUNITYPROJECT
  set
     NAME = @PROJECTNAME,
     DESCRIPTION = @DESCRIPTION,
     GOAL = @GOAL,
         SPONSORGOAL = @SPONSORGOAL,
     SPROPPPROJECTCATEGORYCODEID = @SPROPPPROJECTCATEGORYCODE,
     DESIGNATIONID = @DESIGNATIONID,
     CHANGEDBYID = @CHANGEAGENTID,
     DATECHANGED = @CURRENTDATE
  where
     ID = @ID

  exec dbo.USP_SPONSORSHIPOPPORTUNITY_UNLOCK @ID, 0, @CHANGEAGENTID
end try

begin catch
    exec dbo.USP_RAISE_ERROR
    return 1
end catch

return 0