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