USP_DESIGNATION_GETDESIGNATIONMAPPING_UPDATEFROMXML
Used to update a set of records defined by UFN_DESIGNATION_GETDESIGNATIONMAPPING from the given xml string.
Parameters
Parameter | Parameter Type | Mode | Description |
---|---|---|---|
@DESIGNATIONLEVEL1ID | uniqueidentifier | IN | |
@XML | xml | IN | |
@CHANGEAGENTID | uniqueidentifier | IN | |
@CHANGEDATE | datetime | IN |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 3/19/2013 1:28:41 AM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=3.0.504.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE PROCEDURE dbo.USP_DESIGNATION_GETDESIGNATIONMAPPING_UPDATEFROMXML
(
@DESIGNATIONLEVEL1ID 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 (
[ACCOUNTNUMBER] nvarchar(100),
[ID] uniqueidentifier,
[PROJECTCODE] nvarchar(100))
insert into @TempTbl select
[ACCOUNTNUMBER],
[ID],
[PROJECTCODE]
from dbo.UFN_DESIGNATION_GETDESIGNATIONMAPPING_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.[DESIGNATION] where [DESIGNATION].ID in
(select ID from dbo.UFN_DESIGNATION_GETDESIGNATIONMAPPING
(
@DESIGNATIONLEVEL1ID
)
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 [DESIGNATION]
set [DESIGNATION].[ACCOUNTNUMBER]=temp.[ACCOUNTNUMBER],
[DESIGNATION].[ID]=temp.[ID],
[DESIGNATION].[PROJECTCODE]=temp.[PROJECTCODE],
[DESIGNATION].CHANGEDBYID = @CHANGEAGENTID,
[DESIGNATION].DATECHANGED = @CHANGEDATE
from dbo.[DESIGNATION] inner join @TempTbl as [temp] on [DESIGNATION].ID = [temp].ID
where ([DESIGNATION].[ACCOUNTNUMBER]<>temp.[ACCOUNTNUMBER]) or
([DESIGNATION].[ACCOUNTNUMBER] is null and temp.[ACCOUNTNUMBER] is not null) or
([DESIGNATION].[ACCOUNTNUMBER] is not null and temp.[ACCOUNTNUMBER] is null) or
([DESIGNATION].[ID]<>temp.[ID]) or
([DESIGNATION].[ID] is null and temp.[ID] is not null) or
([DESIGNATION].[ID] is not null and temp.[ID] is null) or
([DESIGNATION].[PROJECTCODE]<>temp.[PROJECTCODE]) or
([DESIGNATION].[PROJECTCODE] is null and temp.[PROJECTCODE] is not null) or
([DESIGNATION].[PROJECTCODE] is not null and temp.[PROJECTCODE] is null)
if @@Error <> 0
return 3;
-- insert new items
insert into [DESIGNATION]
([DESIGNATIONLEVEL1ID],
[ACCOUNTNUMBER],
[ID],
[PROJECTCODE],
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED)
select @DESIGNATIONLEVEL1ID,
[ACCOUNTNUMBER],
[ID],
[PROJECTCODE],
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE
from @TempTbl as [temp]
where not exists (select ID from dbo.[DESIGNATION] as data where data.ID = [temp].ID)
if @@Error <> 0
return 4;
return 0;