USP_PROSPECTSEGMENTATION_SECTIONS_SECONDARY_ADDFROMXML
Used to add a set of records defined by UFN_PROSPECTSEGMENTATION_SECTIONS_SECONDARY from the given xml string.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @VARIABLEID | uniqueidentifier | IN | |
| @XML | xml | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @CHANGEDATE | datetime | IN |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 1/27/2010 6:56:16 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=2.5.465.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE PROCEDURE dbo.USP_PROSPECTSEGMENTATION_SECTIONS_SECONDARY_ADDFROMXML
(
@VARIABLEID 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 (
[ESTIMATEDWEALTH2HIGHID] uniqueidentifier,
[ESTIMATEDWEALTH2LOWID] uniqueidentifier,
[ESTIMATEDWEALTHHIGHID] uniqueidentifier,
[ESTIMATEDWEALTHLOWID] uniqueidentifier,
[ID] uniqueidentifier,
[LABEL] nvarchar(20),
[LABEL2] nvarchar(20),
[MAJORGIVINGCAPACITY2HIGHID] uniqueidentifier,
[MAJORGIVINGCAPACITY2LOWID] uniqueidentifier,
[MAJORGIVINGCAPACITYHIGHID] uniqueidentifier,
[MAJORGIVINGCAPACITYLOWID] uniqueidentifier,
[RANGE2HIGH] int,
[RANGE2LOW] int,
[RANGEHIGH] int,
[RANGELOW] int,
[SEQUENCE] int,
[TARGETGIFTRANGE2HIGHID] uniqueidentifier,
[TARGETGIFTRANGE2LOWID] uniqueidentifier,
[TARGETGIFTRANGEHIGHID] uniqueidentifier,
[TARGETGIFTRANGELOWID] uniqueidentifier,
[WEALTHESTIMATORRATING2HIGHID] uniqueidentifier,
[WEALTHESTIMATORRATING2LOWID] uniqueidentifier,
[WEALTHESTIMATORRATINGHIGHID] uniqueidentifier,
[WEALTHESTIMATORRATINGLOWID] uniqueidentifier)
insert into @TempTbl select
[ESTIMATEDWEALTH2HIGHID],
[ESTIMATEDWEALTH2LOWID],
[ESTIMATEDWEALTHHIGHID],
[ESTIMATEDWEALTHLOWID],
[ID],
[LABEL],
[LABEL2],
[MAJORGIVINGCAPACITY2HIGHID],
[MAJORGIVINGCAPACITY2LOWID],
[MAJORGIVINGCAPACITYHIGHID],
[MAJORGIVINGCAPACITYLOWID],
[RANGE2HIGH],
[RANGE2LOW],
[RANGEHIGH],
[RANGELOW],
[SEQUENCE],
[TARGETGIFTRANGE2HIGHID],
[TARGETGIFTRANGE2LOWID],
[TARGETGIFTRANGEHIGHID],
[TARGETGIFTRANGELOWID],
[WEALTHESTIMATORRATING2HIGHID],
[WEALTHESTIMATORRATING2LOWID],
[WEALTHESTIMATORRATINGHIGHID],
[WEALTHESTIMATORRATINGLOWID]
from dbo.UFN_PROSPECTSEGMENTATION_SECTIONS_SECONDARY_FROMITEMLISTXML(@XML)
update @TempTbl set ID = newid() where (ID is null) or (ID = '00000000-0000-0000-0000-000000000000');
if @@Error <> 0
return 1;
-- insert new items
insert into [PROSPECTSEGMENTATIONSECTION]
([VARIABLEID],
[ESTIMATEDWEALTH2HIGHID],
[ESTIMATEDWEALTH2LOWID],
[ESTIMATEDWEALTHHIGHID],
[ESTIMATEDWEALTHLOWID],
[ID],
[LABEL],
[LABEL2],
[MAJORGIVINGCAPACITY2HIGHID],
[MAJORGIVINGCAPACITY2LOWID],
[MAJORGIVINGCAPACITYHIGHID],
[MAJORGIVINGCAPACITYLOWID],
[RANGE2HIGH],
[RANGE2LOW],
[RANGEHIGH],
[RANGELOW],
[SEQUENCE],
[TARGETGIFTRANGE2HIGHID],
[TARGETGIFTRANGE2LOWID],
[TARGETGIFTRANGEHIGHID],
[TARGETGIFTRANGELOWID],
[WEALTHESTIMATORRATING2HIGHID],
[WEALTHESTIMATORRATING2LOWID],
[WEALTHESTIMATORRATINGHIGHID],
[WEALTHESTIMATORRATINGLOWID],
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED)
select @VARIABLEID,
[ESTIMATEDWEALTH2HIGHID],
[ESTIMATEDWEALTH2LOWID],
[ESTIMATEDWEALTHHIGHID],
[ESTIMATEDWEALTHLOWID],
[ID],
[LABEL],
[LABEL2],
[MAJORGIVINGCAPACITY2HIGHID],
[MAJORGIVINGCAPACITY2LOWID],
[MAJORGIVINGCAPACITYHIGHID],
[MAJORGIVINGCAPACITYLOWID],
[RANGE2HIGH],
[RANGE2LOW],
[RANGEHIGH],
[RANGELOW],
[SEQUENCE],
[TARGETGIFTRANGE2HIGHID],
[TARGETGIFTRANGE2LOWID],
[TARGETGIFTRANGEHIGHID],
[TARGETGIFTRANGELOWID],
[WEALTHESTIMATORRATING2HIGHID],
[WEALTHESTIMATORRATING2LOWID],
[WEALTHESTIMATORRATINGHIGHID],
[WEALTHESTIMATORRATINGLOWID],
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE
from @TempTbl as [temp]
if @@Error <> 0
return 2;
return 0;