USP_FAFEVENT_GETREGISTRATIONOPTIONGOALS_UPDATEFROMXML
Used to update a set of records defined by UFN_FAFEVENT_GETREGISTRATIONOPTIONGOALS from the given xml string.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @EVENTPRICEID | uniqueidentifier | IN | |
| @XML | xml | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @CHANGEDATE | datetime | IN |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 2/1/2011 9:06:16 AM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=2.9.1001.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE PROCEDURE dbo.USP_FAFEVENT_GETREGISTRATIONOPTIONGOALS_UPDATEFROMXML
(
@EVENTPRICEID uniqueidentifier,
@XML xml,
@CHANGEAGENTID uniqueidentifier = null,
@CHANGEDATE datetime = null
)
as
set nocount on;
if @CHANGEAGENTID is null
exec USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output
if @CHANGEDATE is null
set @CHANGEDATE = getdate()
-- build a temporary table containing the values from the XML
declare @TempTbl table (
[COMMUNICATIONS] int,
[DONORRETENTION] decimal(7, 4),
[ID] varchar(36),
[ISNOTLOWERCOMMUNICATION] bit,
[ISNOTLOWERDONORRETENTION] bit,
[ISNOTLOWERFUNDRAISING] bit,
[ISNOTLOWERRECRUITEMENT] bit,
[ISNOTLOWERTARGETFUNDRAISING] bit,
[ISNOTLOWERTEAMMEMBERRETENTION] bit,
[ISNOTLOWERTEAMMEMBERS] bit,
[ISNOTLOWERTEAMRETENTION] bit,
[ISNOTLOWERTEAMS] bit,
[ISNOTLOWERUNIT] bit,
[ISNOTLOWERVOLUNTEERRECRUITEMENT] bit,
[LABELFORUNIT] nvarchar(50),
[MINFUNDRAISING] money,
[PARTICIPANTMUSTPAYBALANCE] bit,
[PARTICIPANTPAYSBALANCEMESSAGE] nvarchar(1000),
[RECRUITEMENT] int,
[REGTYPEGOALCODE] tinyint,
[TARGETFUNDRAISING] money,
[TEAMMEMBERRETENTION] decimal(7, 4),
[TEAMMEMBERS] int,
[TEAMRETENTION] decimal(7, 4),
[TEAMS] int,
[UNIT] int,
[VOLUNTEERRECRUITEMENT] int)
insert into @TempTbl select
[COMMUNICATIONS],
[DONORRETENTION],
[ID],
[ISNOTLOWERCOMMUNICATION],
[ISNOTLOWERDONORRETENTION],
[ISNOTLOWERFUNDRAISING],
[ISNOTLOWERRECRUITEMENT],
[ISNOTLOWERTARGETFUNDRAISING],
[ISNOTLOWERTEAMMEMBERRETENTION],
[ISNOTLOWERTEAMMEMBERS],
[ISNOTLOWERTEAMRETENTION],
[ISNOTLOWERTEAMS],
[ISNOTLOWERUNIT],
[ISNOTLOWERVOLUNTEERRECRUITEMENT],
[LABELFORUNIT],
[MINFUNDRAISING],
[PARTICIPANTMUSTPAYBALANCE],
[PARTICIPANTPAYSBALANCEMESSAGE],
[RECRUITEMENT],
[REGTYPEGOALCODE],
[TARGETFUNDRAISING],
[TEAMMEMBERRETENTION],
[TEAMMEMBERS],
[TEAMRETENTION],
[TEAMS],
[UNIT],
[VOLUNTEERRECRUITEMENT]
from dbo.UFN_FAFEVENT_GETREGISTRATIONOPTIONGOALS_FROMITEMLISTXML(@XML)
update @TempTbl set ID = newid() where (ID is null) or (ID = '00000000-0000-0000-0000-000000000000');
if @@Error <> 0
return 1;
declare @contextCache varbinary(128);
declare @e int;
-- cache current context information
set @contextCache = CONTEXT_INFO();
-- set CONTEXT_INFO to @CHANGEAGENTID
if not @CHANGEAGENTID is null
set CONTEXT_INFO @CHANGEAGENTID;
-- delete any items that no longer exist in the XML table
delete from dbo.[FAFREGISTRATIONTYPEGOAL] where [FAFREGISTRATIONTYPEGOAL].ID in
(select ID from dbo.UFN_FAFEVENT_GETREGISTRATIONOPTIONGOALS
(
@EVENTPRICEID
)
EXCEPT select ID from @TempTbl)
select @e=@@error;
-- reset CONTEXT_INFO to previous value
if not @contextCache is null
set CONTEXT_INFO @contextCache;
if @e <> 0
return 2;
-- update the items that exist in the XML table and the db
update [FAFREGISTRATIONTYPEGOAL]
set [FAFREGISTRATIONTYPEGOAL].[COMMUNICATIONS]=temp.[COMMUNICATIONS],
[FAFREGISTRATIONTYPEGOAL].[DONORRETENTION]=temp.[DONORRETENTION],
[FAFREGISTRATIONTYPEGOAL].[ID]=temp.[ID],
[FAFREGISTRATIONTYPEGOAL].[ISNOTLOWERCOMMUNICATION]=temp.[ISNOTLOWERCOMMUNICATION],
[FAFREGISTRATIONTYPEGOAL].[ISNOTLOWERDONORRETENTION]=temp.[ISNOTLOWERDONORRETENTION],
[FAFREGISTRATIONTYPEGOAL].[ISNOTLOWERFUNDRAISING]=temp.[ISNOTLOWERFUNDRAISING],
[FAFREGISTRATIONTYPEGOAL].[ISNOTLOWERRECRUITEMENT]=temp.[ISNOTLOWERRECRUITEMENT],
[FAFREGISTRATIONTYPEGOAL].[ISNOTLOWERTARGETFUNDRAISING]=temp.[ISNOTLOWERTARGETFUNDRAISING],
[FAFREGISTRATIONTYPEGOAL].[ISNOTLOWERTEAMMEMBERRETENTION]=temp.[ISNOTLOWERTEAMMEMBERRETENTION],
[FAFREGISTRATIONTYPEGOAL].[ISNOTLOWERTEAMMEMBERS]=temp.[ISNOTLOWERTEAMMEMBERS],
[FAFREGISTRATIONTYPEGOAL].[ISNOTLOWERTEAMRETENTION]=temp.[ISNOTLOWERTEAMRETENTION],
[FAFREGISTRATIONTYPEGOAL].[ISNOTLOWERTEAMS]=temp.[ISNOTLOWERTEAMS],
[FAFREGISTRATIONTYPEGOAL].[ISNOTLOWERUNIT]=temp.[ISNOTLOWERUNIT],
[FAFREGISTRATIONTYPEGOAL].[ISNOTLOWERVOLUNTEERRECRUITEMENT]=temp.[ISNOTLOWERVOLUNTEERRECRUITEMENT],
[FAFREGISTRATIONTYPEGOAL].[LABELFORUNIT]=temp.[LABELFORUNIT],
[FAFREGISTRATIONTYPEGOAL].[MINFUNDRAISING]=temp.[MINFUNDRAISING],
[FAFREGISTRATIONTYPEGOAL].[PARTICIPANTMUSTPAYBALANCE]=temp.[PARTICIPANTMUSTPAYBALANCE],
[FAFREGISTRATIONTYPEGOAL].[PARTICIPANTPAYSBALANCEMESSAGE]=temp.[PARTICIPANTPAYSBALANCEMESSAGE],
[FAFREGISTRATIONTYPEGOAL].[RECRUITEMENT]=temp.[RECRUITEMENT],
[FAFREGISTRATIONTYPEGOAL].[REGTYPEGOALCODE]=temp.[REGTYPEGOALCODE],
[FAFREGISTRATIONTYPEGOAL].[TARGETFUNDRAISING]=temp.[TARGETFUNDRAISING],
[FAFREGISTRATIONTYPEGOAL].[TEAMMEMBERRETENTION]=temp.[TEAMMEMBERRETENTION],
[FAFREGISTRATIONTYPEGOAL].[TEAMMEMBERS]=temp.[TEAMMEMBERS],
[FAFREGISTRATIONTYPEGOAL].[TEAMRETENTION]=temp.[TEAMRETENTION],
[FAFREGISTRATIONTYPEGOAL].[TEAMS]=temp.[TEAMS],
[FAFREGISTRATIONTYPEGOAL].[UNIT]=temp.[UNIT],
[FAFREGISTRATIONTYPEGOAL].[VOLUNTEERRECRUITEMENT]=temp.[VOLUNTEERRECRUITEMENT],
[FAFREGISTRATIONTYPEGOAL].CHANGEDBYID = @CHANGEAGENTID,
[FAFREGISTRATIONTYPEGOAL].DATECHANGED = @CHANGEDATE
from dbo.[FAFREGISTRATIONTYPEGOAL] inner join @TempTbl as [temp] on [FAFREGISTRATIONTYPEGOAL].ID = [temp].ID
where ([FAFREGISTRATIONTYPEGOAL].[COMMUNICATIONS]<>temp.[COMMUNICATIONS]) or
([FAFREGISTRATIONTYPEGOAL].[COMMUNICATIONS] is null and temp.[COMMUNICATIONS] is not null) or
([FAFREGISTRATIONTYPEGOAL].[COMMUNICATIONS] is not null and temp.[COMMUNICATIONS] is null) or
([FAFREGISTRATIONTYPEGOAL].[DONORRETENTION]<>temp.[DONORRETENTION]) or
([FAFREGISTRATIONTYPEGOAL].[DONORRETENTION] is null and temp.[DONORRETENTION] is not null) or
([FAFREGISTRATIONTYPEGOAL].[DONORRETENTION] is not null and temp.[DONORRETENTION] is null) or
([FAFREGISTRATIONTYPEGOAL].[ID]<>temp.[ID]) or
([FAFREGISTRATIONTYPEGOAL].[ID] is null and temp.[ID] is not null) or
([FAFREGISTRATIONTYPEGOAL].[ID] is not null and temp.[ID] is null) or
([FAFREGISTRATIONTYPEGOAL].[ISNOTLOWERCOMMUNICATION]<>temp.[ISNOTLOWERCOMMUNICATION]) or
([FAFREGISTRATIONTYPEGOAL].[ISNOTLOWERCOMMUNICATION] is null and temp.[ISNOTLOWERCOMMUNICATION] is not null) or
([FAFREGISTRATIONTYPEGOAL].[ISNOTLOWERCOMMUNICATION] is not null and temp.[ISNOTLOWERCOMMUNICATION] is null) or
([FAFREGISTRATIONTYPEGOAL].[ISNOTLOWERDONORRETENTION]<>temp.[ISNOTLOWERDONORRETENTION]) or
([FAFREGISTRATIONTYPEGOAL].[ISNOTLOWERDONORRETENTION] is null and temp.[ISNOTLOWERDONORRETENTION] is not null) or
([FAFREGISTRATIONTYPEGOAL].[ISNOTLOWERDONORRETENTION] is not null and temp.[ISNOTLOWERDONORRETENTION] is null) or
([FAFREGISTRATIONTYPEGOAL].[ISNOTLOWERFUNDRAISING]<>temp.[ISNOTLOWERFUNDRAISING]) or
([FAFREGISTRATIONTYPEGOAL].[ISNOTLOWERFUNDRAISING] is null and temp.[ISNOTLOWERFUNDRAISING] is not null) or
([FAFREGISTRATIONTYPEGOAL].[ISNOTLOWERFUNDRAISING] is not null and temp.[ISNOTLOWERFUNDRAISING] is null) or
([FAFREGISTRATIONTYPEGOAL].[ISNOTLOWERRECRUITEMENT]<>temp.[ISNOTLOWERRECRUITEMENT]) or
([FAFREGISTRATIONTYPEGOAL].[ISNOTLOWERRECRUITEMENT] is null and temp.[ISNOTLOWERRECRUITEMENT] is not null) or
([FAFREGISTRATIONTYPEGOAL].[ISNOTLOWERRECRUITEMENT] is not null and temp.[ISNOTLOWERRECRUITEMENT] is null) or
([FAFREGISTRATIONTYPEGOAL].[ISNOTLOWERTARGETFUNDRAISING]<>temp.[ISNOTLOWERTARGETFUNDRAISING]) or
([FAFREGISTRATIONTYPEGOAL].[ISNOTLOWERTARGETFUNDRAISING] is null and temp.[ISNOTLOWERTARGETFUNDRAISING] is not null) or
([FAFREGISTRATIONTYPEGOAL].[ISNOTLOWERTARGETFUNDRAISING] is not null and temp.[ISNOTLOWERTARGETFUNDRAISING] is null) or
([FAFREGISTRATIONTYPEGOAL].[ISNOTLOWERTEAMMEMBERRETENTION]<>temp.[ISNOTLOWERTEAMMEMBERRETENTION]) or
([FAFREGISTRATIONTYPEGOAL].[ISNOTLOWERTEAMMEMBERRETENTION] is null and temp.[ISNOTLOWERTEAMMEMBERRETENTION] is not null) or
([FAFREGISTRATIONTYPEGOAL].[ISNOTLOWERTEAMMEMBERRETENTION] is not null and temp.[ISNOTLOWERTEAMMEMBERRETENTION] is null) or
([FAFREGISTRATIONTYPEGOAL].[ISNOTLOWERTEAMMEMBERS]<>temp.[ISNOTLOWERTEAMMEMBERS]) or
([FAFREGISTRATIONTYPEGOAL].[ISNOTLOWERTEAMMEMBERS] is null and temp.[ISNOTLOWERTEAMMEMBERS] is not null) or
([FAFREGISTRATIONTYPEGOAL].[ISNOTLOWERTEAMMEMBERS] is not null and temp.[ISNOTLOWERTEAMMEMBERS] is null) or
([FAFREGISTRATIONTYPEGOAL].[ISNOTLOWERTEAMRETENTION]<>temp.[ISNOTLOWERTEAMRETENTION]) or
([FAFREGISTRATIONTYPEGOAL].[ISNOTLOWERTEAMRETENTION] is null and temp.[ISNOTLOWERTEAMRETENTION] is not null) or
([FAFREGISTRATIONTYPEGOAL].[ISNOTLOWERTEAMRETENTION] is not null and temp.[ISNOTLOWERTEAMRETENTION] is null) or
([FAFREGISTRATIONTYPEGOAL].[ISNOTLOWERTEAMS]<>temp.[ISNOTLOWERTEAMS]) or
([FAFREGISTRATIONTYPEGOAL].[ISNOTLOWERTEAMS] is null and temp.[ISNOTLOWERTEAMS] is not null) or
([FAFREGISTRATIONTYPEGOAL].[ISNOTLOWERTEAMS] is not null and temp.[ISNOTLOWERTEAMS] is null) or
([FAFREGISTRATIONTYPEGOAL].[ISNOTLOWERUNIT]<>temp.[ISNOTLOWERUNIT]) or
([FAFREGISTRATIONTYPEGOAL].[ISNOTLOWERUNIT] is null and temp.[ISNOTLOWERUNIT] is not null) or
([FAFREGISTRATIONTYPEGOAL].[ISNOTLOWERUNIT] is not null and temp.[ISNOTLOWERUNIT] is null) or
([FAFREGISTRATIONTYPEGOAL].[ISNOTLOWERVOLUNTEERRECRUITEMENT]<>temp.[ISNOTLOWERVOLUNTEERRECRUITEMENT]) or
([FAFREGISTRATIONTYPEGOAL].[ISNOTLOWERVOLUNTEERRECRUITEMENT] is null and temp.[ISNOTLOWERVOLUNTEERRECRUITEMENT] is not null) or
([FAFREGISTRATIONTYPEGOAL].[ISNOTLOWERVOLUNTEERRECRUITEMENT] is not null and temp.[ISNOTLOWERVOLUNTEERRECRUITEMENT] is null) or
([FAFREGISTRATIONTYPEGOAL].[LABELFORUNIT]<>temp.[LABELFORUNIT]) or
([FAFREGISTRATIONTYPEGOAL].[LABELFORUNIT] is null and temp.[LABELFORUNIT] is not null) or
([FAFREGISTRATIONTYPEGOAL].[LABELFORUNIT] is not null and temp.[LABELFORUNIT] is null) or
([FAFREGISTRATIONTYPEGOAL].[MINFUNDRAISING]<>temp.[MINFUNDRAISING]) or
([FAFREGISTRATIONTYPEGOAL].[MINFUNDRAISING] is null and temp.[MINFUNDRAISING] is not null) or
([FAFREGISTRATIONTYPEGOAL].[MINFUNDRAISING] is not null and temp.[MINFUNDRAISING] is null) or
([FAFREGISTRATIONTYPEGOAL].[PARTICIPANTMUSTPAYBALANCE]<>temp.[PARTICIPANTMUSTPAYBALANCE]) or
([FAFREGISTRATIONTYPEGOAL].[PARTICIPANTMUSTPAYBALANCE] is null and temp.[PARTICIPANTMUSTPAYBALANCE] is not null) or
([FAFREGISTRATIONTYPEGOAL].[PARTICIPANTMUSTPAYBALANCE] is not null and temp.[PARTICIPANTMUSTPAYBALANCE] is null) or
([FAFREGISTRATIONTYPEGOAL].[PARTICIPANTPAYSBALANCEMESSAGE]<>temp.[PARTICIPANTPAYSBALANCEMESSAGE]) or
([FAFREGISTRATIONTYPEGOAL].[PARTICIPANTPAYSBALANCEMESSAGE] is null and temp.[PARTICIPANTPAYSBALANCEMESSAGE] is not null) or
([FAFREGISTRATIONTYPEGOAL].[PARTICIPANTPAYSBALANCEMESSAGE] is not null and temp.[PARTICIPANTPAYSBALANCEMESSAGE] is null) or
([FAFREGISTRATIONTYPEGOAL].[RECRUITEMENT]<>temp.[RECRUITEMENT]) or
([FAFREGISTRATIONTYPEGOAL].[RECRUITEMENT] is null and temp.[RECRUITEMENT] is not null) or
([FAFREGISTRATIONTYPEGOAL].[RECRUITEMENT] is not null and temp.[RECRUITEMENT] is null) or
([FAFREGISTRATIONTYPEGOAL].[REGTYPEGOALCODE]<>temp.[REGTYPEGOALCODE]) or
([FAFREGISTRATIONTYPEGOAL].[REGTYPEGOALCODE] is null and temp.[REGTYPEGOALCODE] is not null) or
([FAFREGISTRATIONTYPEGOAL].[REGTYPEGOALCODE] is not null and temp.[REGTYPEGOALCODE] is null) or
([FAFREGISTRATIONTYPEGOAL].[TARGETFUNDRAISING]<>temp.[TARGETFUNDRAISING]) or
([FAFREGISTRATIONTYPEGOAL].[TARGETFUNDRAISING] is null and temp.[TARGETFUNDRAISING] is not null) or
([FAFREGISTRATIONTYPEGOAL].[TARGETFUNDRAISING] is not null and temp.[TARGETFUNDRAISING] is null) or
([FAFREGISTRATIONTYPEGOAL].[TEAMMEMBERRETENTION]<>temp.[TEAMMEMBERRETENTION]) or
([FAFREGISTRATIONTYPEGOAL].[TEAMMEMBERRETENTION] is null and temp.[TEAMMEMBERRETENTION] is not null) or
([FAFREGISTRATIONTYPEGOAL].[TEAMMEMBERRETENTION] is not null and temp.[TEAMMEMBERRETENTION] is null) or
([FAFREGISTRATIONTYPEGOAL].[TEAMMEMBERS]<>temp.[TEAMMEMBERS]) or
([FAFREGISTRATIONTYPEGOAL].[TEAMMEMBERS] is null and temp.[TEAMMEMBERS] is not null) or
([FAFREGISTRATIONTYPEGOAL].[TEAMMEMBERS] is not null and temp.[TEAMMEMBERS] is null) or
([FAFREGISTRATIONTYPEGOAL].[TEAMRETENTION]<>temp.[TEAMRETENTION]) or
([FAFREGISTRATIONTYPEGOAL].[TEAMRETENTION] is null and temp.[TEAMRETENTION] is not null) or
([FAFREGISTRATIONTYPEGOAL].[TEAMRETENTION] is not null and temp.[TEAMRETENTION] is null) or
([FAFREGISTRATIONTYPEGOAL].[TEAMS]<>temp.[TEAMS]) or
([FAFREGISTRATIONTYPEGOAL].[TEAMS] is null and temp.[TEAMS] is not null) or
([FAFREGISTRATIONTYPEGOAL].[TEAMS] is not null and temp.[TEAMS] is null) or
([FAFREGISTRATIONTYPEGOAL].[UNIT]<>temp.[UNIT]) or
([FAFREGISTRATIONTYPEGOAL].[UNIT] is null and temp.[UNIT] is not null) or
([FAFREGISTRATIONTYPEGOAL].[UNIT] is not null and temp.[UNIT] is null) or
([FAFREGISTRATIONTYPEGOAL].[VOLUNTEERRECRUITEMENT]<>temp.[VOLUNTEERRECRUITEMENT]) or
([FAFREGISTRATIONTYPEGOAL].[VOLUNTEERRECRUITEMENT] is null and temp.[VOLUNTEERRECRUITEMENT] is not null) or
([FAFREGISTRATIONTYPEGOAL].[VOLUNTEERRECRUITEMENT] is not null and temp.[VOLUNTEERRECRUITEMENT] is null)
if @@Error <> 0
return 3;
-- insert new items
insert into [FAFREGISTRATIONTYPEGOAL]
([EVENTPRICEID],
[COMMUNICATIONS],
[DONORRETENTION],
[ID],
[ISNOTLOWERCOMMUNICATION],
[ISNOTLOWERDONORRETENTION],
[ISNOTLOWERFUNDRAISING],
[ISNOTLOWERRECRUITEMENT],
[ISNOTLOWERTARGETFUNDRAISING],
[ISNOTLOWERTEAMMEMBERRETENTION],
[ISNOTLOWERTEAMMEMBERS],
[ISNOTLOWERTEAMRETENTION],
[ISNOTLOWERTEAMS],
[ISNOTLOWERUNIT],
[ISNOTLOWERVOLUNTEERRECRUITEMENT],
[LABELFORUNIT],
[MINFUNDRAISING],
[PARTICIPANTMUSTPAYBALANCE],
[PARTICIPANTPAYSBALANCEMESSAGE],
[RECRUITEMENT],
[REGTYPEGOALCODE],
[TARGETFUNDRAISING],
[TEAMMEMBERRETENTION],
[TEAMMEMBERS],
[TEAMRETENTION],
[TEAMS],
[UNIT],
[VOLUNTEERRECRUITEMENT],
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED)
select @EVENTPRICEID,
[COMMUNICATIONS],
[DONORRETENTION],
[ID],
[ISNOTLOWERCOMMUNICATION],
[ISNOTLOWERDONORRETENTION],
[ISNOTLOWERFUNDRAISING],
[ISNOTLOWERRECRUITEMENT],
[ISNOTLOWERTARGETFUNDRAISING],
[ISNOTLOWERTEAMMEMBERRETENTION],
[ISNOTLOWERTEAMMEMBERS],
[ISNOTLOWERTEAMRETENTION],
[ISNOTLOWERTEAMS],
[ISNOTLOWERUNIT],
[ISNOTLOWERVOLUNTEERRECRUITEMENT],
[LABELFORUNIT],
[MINFUNDRAISING],
[PARTICIPANTMUSTPAYBALANCE],
[PARTICIPANTPAYSBALANCEMESSAGE],
[RECRUITEMENT],
[REGTYPEGOALCODE],
[TARGETFUNDRAISING],
[TEAMMEMBERRETENTION],
[TEAMMEMBERS],
[TEAMRETENTION],
[TEAMS],
[UNIT],
[VOLUNTEERRECRUITEMENT],
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE
from @TempTbl as [temp]
where not exists (select ID from dbo.[FAFREGISTRATIONTYPEGOAL] as data where data.ID = [temp].ID)
if @@Error <> 0
return 4;
return 0;