USP_DATAFORMTEMPLATE_EDIT_STAFF_RESOURCE

The save procedure used by the edit dataform template "Staff Resource 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.
@NAME	nvarchar(50)	IN	Name
@DESCRIPTION	nvarchar(255)	IN	Description
@QUANTITY	int	IN	Quantity available
@CAPACITYPERRESOURCE	int	IN	Capacity per resource
@SCREENPLANID	uniqueidentifier	IN	Screening plan
@PRICINGSTRUCTURECODE	tinyint	IN	Pricing structure
@PRICE	money	IN	Cost per resource
Definition

Copy

                CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_STAFF_RESOURCE
                (
                    @ID uniqueidentifier,
                    @CHANGEAGENTID uniqueidentifier = null,
                    @NAME nvarchar(50),
                    @DESCRIPTION nvarchar(255),
                    @QUANTITY int,
                    @CAPACITYPERRESOURCE int,
                    @SCREENPLANID uniqueidentifier,
                    @PRICINGSTRUCTURECODE tinyint,
                    @PRICE money
                )
                as

                    set nocount on;

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

                    declare @CURRENTDATE datetime
                    set @CURRENTDATE = getdate()

                    begin try
                        declare @MAXRESOURCEQUANTITY int
                        select @MAXRESOURCEQUANTITY = coalesce(max(QUANTITYNEEDED),0)
                        from dbo.GROUPTYPEREQUIREDSTAFFRESOURCE
                        where VOLUNTEERTYPEID = @ID

                        if @QUANTITY < @MAXRESOURCEQUANTITY
                        begin
                            declare @GROUPTYPE nvarchar(100)
                            select @GROUPTYPE = coalesce(DESCRIPTION,'')
                            from dbo.GROUPTYPEREQUIREDSTAFFRESOURCE
                            inner join dbo.GROUPSALESGROUPTYPECODE on
                                GROUPSALESGROUPTYPECODE.ID = GROUPTYPEREQUIREDSTAFFRESOURCE.GROUPSALESGROUPTYPECODEID
                            where
                                VOLUNTEERTYPEID = @ID and
                                QUANTITYNEEDED = @MAXRESOURCEQUANTITY

                            declare @ERRORMESSAGE nvarchar(200)
                            set @ERRORMESSAGE = '"' + @GROUPTYPE + '" group type requires a quantity of ' + convert(nvarchar(10), @MAXRESOURCEQUANTITY) + '.' 

                            raiserror(@ERRORMESSAGE, 13, 1);
                        end

                        update dbo.VOLUNTEERTYPE set
                            NAME = @NAME,
                            DESCRIPTION = @DESCRIPTION,
                            QUANTITY = @QUANTITY,
                            CAPACITYPERRESOURCE = @CAPACITYPERRESOURCE,
                            SCREENPLANID = @SCREENPLANID,

                            CHANGEDBYID = @CHANGEAGENTID,
                            DATECHANGED = @CURRENTDATE
                        where ID = @ID;

                        if exists(select 1 from dbo.VOLUNTEERTYPEPRICING where ID = @ID)
                            update dbo.VOLUNTEERTYPEPRICING set
                                PRICINGSTRUCTURECODE = @PRICINGSTRUCTURECODE,
                                PRICE = @PRICE,
                                CHANGEDBYID = @CHANGEAGENTID,
                                DATECHANGED = @CURRENTDATE
                            where ID = @ID;
                        else
                            insert into dbo.[VOLUNTEERTYPEPRICING]
                            (
                                ID,
                                PRICINGSTRUCTURECODE,
                                PRICE,
                                ADDEDBYID,
                                CHANGEDBYID,
                                DATEADDED,
                                DATECHANGED
                            )
                            values
                            (
                                @ID,
                                @PRICINGSTRUCTURECODE,
                                @PRICE,
                                @CHANGEAGENTID,
                                @CHANGEAGENTID,
                                @CURRENTDATE,
                                @CURRENTDATE
                            );
                    end try
                    begin catch
                        exec dbo.USP_RAISE_ERROR
                        return 1
                    end catch

                return 0;