USP_CAMPAIGN_DESIGNATIONS_UPDATEFROMXML
Used to update a set of records defined by UFN_CAMPAIGN_DESIGNATIONS from the given xml string.
Parameters
Parameter | Parameter Type | Mode | Description |
---|---|---|---|
@CAMPAIGNID | uniqueidentifier | IN | |
@XML | xml | IN | |
@CHANGEAGENTID | uniqueidentifier | IN | |
@CHANGEDATE | datetime | IN |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 12/17/2008 2:38:49 AM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=1.7.1271.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE PROCEDURE dbo.USP_CAMPAIGN_DESIGNATIONS_UPDATEFROMXML
(
@CAMPAIGNID 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 (
[CAMPAIGNSUBPRIORITYID] uniqueidentifier,
[DATEFROM] datetime,
[DATETO] datetime,
[DESIGNATIONID] uniqueidentifier,
[ID] uniqueidentifier,
[SEQUENCE] int)
insert into @TempTbl select
[CAMPAIGNSUBPRIORITYID],
[DATEFROM],
[DATETO],
[DESIGNATIONID],
[ID],
[SEQUENCE]
from dbo.UFN_CAMPAIGN_DESIGNATIONS_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.[DESIGNATIONCAMPAIGN] where [DESIGNATIONCAMPAIGN].ID in
(select ID from dbo.UFN_CAMPAIGN_DESIGNATIONS
(
@CAMPAIGNID
)
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 [DESIGNATIONCAMPAIGN]
set [DESIGNATIONCAMPAIGN].[CAMPAIGNSUBPRIORITYID]=temp.[CAMPAIGNSUBPRIORITYID],
[DESIGNATIONCAMPAIGN].[DATEFROM]=temp.[DATEFROM],
[DESIGNATIONCAMPAIGN].[DATETO]=temp.[DATETO],
[DESIGNATIONCAMPAIGN].[DESIGNATIONID]=temp.[DESIGNATIONID],
[DESIGNATIONCAMPAIGN].[ID]=temp.[ID],
[DESIGNATIONCAMPAIGN].[SEQUENCE]=temp.[SEQUENCE],
[DESIGNATIONCAMPAIGN].CHANGEDBYID = @CHANGEAGENTID,
[DESIGNATIONCAMPAIGN].DATECHANGED = @CHANGEDATE
from dbo.[DESIGNATIONCAMPAIGN] inner join @TempTbl as [temp] on [DESIGNATIONCAMPAIGN].ID = [temp].ID
where ([DESIGNATIONCAMPAIGN].[CAMPAIGNSUBPRIORITYID]<>temp.[CAMPAIGNSUBPRIORITYID]) or
([DESIGNATIONCAMPAIGN].[CAMPAIGNSUBPRIORITYID] is null and temp.[CAMPAIGNSUBPRIORITYID] is not null) or
([DESIGNATIONCAMPAIGN].[CAMPAIGNSUBPRIORITYID] is not null and temp.[CAMPAIGNSUBPRIORITYID] is null) or
([DESIGNATIONCAMPAIGN].[DATEFROM]<>temp.[DATEFROM]) or
([DESIGNATIONCAMPAIGN].[DATEFROM] is null and temp.[DATEFROM] is not null) or
([DESIGNATIONCAMPAIGN].[DATEFROM] is not null and temp.[DATEFROM] is null) or
([DESIGNATIONCAMPAIGN].[DATETO]<>temp.[DATETO]) or
([DESIGNATIONCAMPAIGN].[DATETO] is null and temp.[DATETO] is not null) or
([DESIGNATIONCAMPAIGN].[DATETO] is not null and temp.[DATETO] is null) or
([DESIGNATIONCAMPAIGN].[DESIGNATIONID]<>temp.[DESIGNATIONID]) or
([DESIGNATIONCAMPAIGN].[DESIGNATIONID] is null and temp.[DESIGNATIONID] is not null) or
([DESIGNATIONCAMPAIGN].[DESIGNATIONID] is not null and temp.[DESIGNATIONID] is null) or
([DESIGNATIONCAMPAIGN].[ID]<>temp.[ID]) or
([DESIGNATIONCAMPAIGN].[ID] is null and temp.[ID] is not null) or
([DESIGNATIONCAMPAIGN].[ID] is not null and temp.[ID] is null) or
([DESIGNATIONCAMPAIGN].[SEQUENCE]<>temp.[SEQUENCE]) or
([DESIGNATIONCAMPAIGN].[SEQUENCE] is null and temp.[SEQUENCE] is not null) or
([DESIGNATIONCAMPAIGN].[SEQUENCE] is not null and temp.[SEQUENCE] is null)
if @@Error <> 0
return 3;
-- insert new items
insert into [DESIGNATIONCAMPAIGN]
([CAMPAIGNID],
[CAMPAIGNSUBPRIORITYID],
[DATEFROM],
[DATETO],
[DESIGNATIONID],
[ID],
[SEQUENCE],
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED)
select @CAMPAIGNID,
[CAMPAIGNSUBPRIORITYID],
[DATEFROM],
[DATETO],
[DESIGNATIONID],
[ID],
[SEQUENCE],
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE
from @TempTbl as [temp]
where not exists (select ID from dbo.[DESIGNATIONCAMPAIGN] as data where data.ID = [temp].ID)
if @@Error <> 0
return 4;
return 0;