USP_DATAFORMTEMPLATE_ADD_PROSPECTSEGMENTATION
The save procedure used by the add dataform template "Prospect Segmentation Add Form".
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | INOUT | The output parameter indicating the ID of the record added. |
| @CHANGEAGENTID | uniqueidentifier | IN | Input parameter indicating the ID of the change agent invoking the procedure. |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
| @NAME | nvarchar(100) | IN | Name |
| @DESCRIPTION | nvarchar(255) | IN | Description |
| @OTHERSCANMODIFY | bit | IN | Others can modify |
| @RESEARCHGROUPID | uniqueidentifier | IN | Research group |
| @IDSETREGISTERID | uniqueidentifier | IN | Selection |
| @PRIMARYVARIABLE | tinyint | IN | Select score/rating |
| @SECONDARYVARIABLE1 | tinyint | IN | Select score/rating |
| @SECONDARYVARIABLE2 | tinyint | IN | Secondary variable 2 |
| @PRIMARYSECTIONS | xml | IN | |
| @SECONDARYSECTIONS1 | xml | IN | |
| @SECONDARYSECTIONS2 | xml | IN |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_PROSPECTSEGMENTATION (
@ID uniqueidentifier = null output,
@CHANGEAGENTID uniqueidentifier = null,
@CURRENTAPPUSERID uniqueidentifier,
@NAME nvarchar(100),
@DESCRIPTION nvarchar(255) = null,
@OTHERSCANMODIFY bit = 1,
@RESEARCHGROUPID uniqueidentifier = null,
@IDSETREGISTERID uniqueidentifier = null,
@PRIMARYVARIABLE tinyint,
@SECONDARYVARIABLE1 tinyint,
@SECONDARYVARIABLE2 tinyint = 0,
@PRIMARYSECTIONS xml = null,
@SECONDARYSECTIONS1 xml = null,
@SECONDARYSECTIONS2 xml = null
) as
set nocount on;
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
if @ID is null
set @ID = newid();
declare
@InfoMsg nvarchar(100),
@ErrorMessage nvarchar(1000),
@ErrorSeverity int,
@ErrorState int;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
begin try
--Validate the Section Counts
declare @PRIMARYSECTIONSCOUNT int;
declare @SECONDARYSECTIONS1COUNT int;
declare @SECONDARYSECTIONS2COUNT int;
select
@PRIMARYSECTIONSCOUNT = COUNT(T.c.value('(SEQUENCE)[1]','int'))
from
@PRIMARYSECTIONS.nodes('/PRIMARYSECTIONS/ITEM') T(c)
select
@SECONDARYSECTIONS1COUNT = COUNT(T.c.value('(SEQUENCE)[1]','int'))
from
@SECONDARYSECTIONS1.nodes('/SECONDARYSECTIONS1/ITEM') T(c)
select
@SECONDARYSECTIONS2COUNT = COUNT(T.c.value('(SEQUENCE)[1]','int'))
from
@SECONDARYSECTIONS2.nodes('/SECONDARYSECTIONS2/ITEM') T(c)
if @PRIMARYSECTIONSCOUNT < 1
raiserror ('ERR_PRIMARYSECTIONS_EMPTY',13,1);
if @SECONDARYSECTIONS1COUNT < 1
raiserror ('ERR_SECONDARYSECTIONS1_EMPTY',13,1);
if @SECONDARYSECTIONS2COUNT < 1 and @SECONDARYVARIABLE2 > 0
raiserror ('ERR_SECONDARYSECTIONS2_EMPTY',13,1);
if @SECONDARYSECTIONS1COUNT < @SECONDARYSECTIONS2COUNT
raiserror ('ERR_SECONDARYSECTIONS2_TOOMANY',13,1);
--Create Prospect Segmentation
insert into dbo.PROSPECTSEGMENTATION(
ID,
NAME,
DESCRIPTION,
OWNERID,
OTHERSCANMODIFY,
RESEARCHGROUPID,
IDSETREGISTERID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)values(
@ID,
@NAME,
@DESCRIPTION,
@CURRENTAPPUSERID,
@OTHERSCANMODIFY,
@RESEARCHGROUPID,
@IDSETREGISTERID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
);
--Create Primary Variable
declare @PRIMARYVARIABLEID uniqueidentifier
set @PRIMARYVARIABLEID =newid();
insert into dbo.PROSPECTSEGMENTATIONVARIABLE(
ID,
TYPECODE,
ISPRIMARY,
PROSPECTSEGMENTATIONID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)values(
@PRIMARYVARIABLEID,
@PRIMARYVARIABLE,
1,
@ID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
)
set @PRIMARYSECTIONS =
(select
T.c.value('(ID)[1]','uniqueidentifier') AS 'ID',
T.c.value('(SEQUENCE)[1]','int') AS 'SEQUENCE',
T.c.value('(LABEL)[1]','nvarchar(20)') AS 'LABEL',
isnull(T.c.value('(RANGELOW)[1]','int'),-1) AS 'RANGELOW',
isnull(T.c.value('(RANGEHIGH)[1]','int'), -1) AS 'RANGEHIGH',
T.c.value('(ESTIMATEDWEALTHLOWID)[1]','uniqueidentifier') AS 'ESTIMATEDWEALTHLOWID',
T.c.value('(ESTIMATEDWEALTHHIGHID)[1]','uniqueidentifier') AS 'ESTIMATEDWEALTHHIGHID',
T.c.value('(MAJORGIVINGCAPACITYLOWID)[1]','uniqueidentifier') AS 'MAJORGIVINGCAPACITYLOWID',
T.c.value('(MAJORGIVINGCAPACITYHIGHID)[1]','uniqueidentifier') AS 'MAJORGIVINGCAPACITYHIGHID',
T.c.value('(TARGETGIFTRANGELOWID)[1]','uniqueidentifier') AS 'TARGETGIFTRANGELOWID',
T.c.value('(TARGETGIFTRANGEHIGHID)[1]','uniqueidentifier') AS 'TARGETGIFTRANGEHIGHID',
T.c.value('(WEALTHESTIMATORRATINGLOWID)[1]','uniqueidentifier') AS 'WEALTHESTIMATORRATINGLOWID',
T.c.value('(WEALTHESTIMATORRATINGHIGHID)[1]','uniqueidentifier') AS 'WEALTHESTIMATORRATINGHIGHID'
from
@PRIMARYSECTIONS.nodes('/PRIMARYSECTIONS/ITEM') T(c)
for
xml raw('ITEM'),type,elements,root('PRIMARYSECTIONS'),BINARY BASE64
)
begin try
exec dbo.USP_PROSPECTSEGMENTATION_SECTIONS_PRIMARY_ADDFROMXML @PRIMARYVARIABLEID,
@PRIMARYSECTIONS,
@CHANGEAGENTID;
end try
begin catch
set @InfoMsg='BBERR_ORIGINAL_ERROR:50001';
raiserror (@InfoMsg,1,11);
select
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
raiserror(@ErrorMessage,@ErrorSeverity,@ErrorState);
return 1;
end catch
--Create Secondary Variable
declare @SECONDARYVARIABLEID uniqueidentifier
set @SECONDARYVARIABLEID =newid();
insert into dbo.PROSPECTSEGMENTATIONVARIABLE(
ID,
TYPECODE,
TYPE2CODE,
ISPRIMARY,
PROSPECTSEGMENTATIONID,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED
)values(
@SECONDARYVARIABLEID,
@SECONDARYVARIABLE1,
@SECONDARYVARIABLE2,
0,
@ID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CURRENTDATE,
@CURRENTDATE
)
declare @SECONDARYSECTIONS xml
set @SECONDARYSECTIONS =
(select
T.c.value('(ID)[1]','uniqueidentifier') AS 'ID',
T.c.value('(SEQUENCE)[1]','int') AS 'SEQUENCE',
T.c.value('(LABEL)[1]','nvarchar(20)') AS 'LABEL',
isnull(T.c.value('(RANGELOW)[1]','int'),-1) AS 'RANGELOW',
isnull(T.c.value('(RANGEHIGH)[1]','int'), -1) AS 'RANGEHIGH',
T.c.value('(ESTIMATEDWEALTHLOWID)[1]','uniqueidentifier') AS 'ESTIMATEDWEALTHLOWID',
T.c.value('(ESTIMATEDWEALTHHIGHID)[1]','uniqueidentifier') AS 'ESTIMATEDWEALTHHIGHID',
T.c.value('(MAJORGIVINGCAPACITYLOWID)[1]','uniqueidentifier') AS 'MAJORGIVINGCAPACITYLOWID',
T.c.value('(MAJORGIVINGCAPACITYHIGHID)[1]','uniqueidentifier') AS 'MAJORGIVINGCAPACITYHIGHID',
T.c.value('(TARGETGIFTRANGELOWID)[1]','uniqueidentifier') AS 'TARGETGIFTRANGELOWID',
T.c.value('(TARGETGIFTRANGEHIGHID)[1]','uniqueidentifier') AS 'TARGETGIFTRANGEHIGHID',
T.c.value('(WEALTHESTIMATORRATINGLOWID)[1]','uniqueidentifier') AS 'WEALTHESTIMATORRATINGLOWID',
T.c.value('(WEALTHESTIMATORRATINGHIGHID)[1]','uniqueidentifier') AS 'WEALTHESTIMATORRATINGHIGHID',
isnull(T2.c.value('(LABEL)[1]','nvarchar(20)'),'') AS 'LABEL2',
isnull(T2.c.value('(RANGELOW)[1]','int'),-1) AS 'RANGE2LOW',
isnull(T2.c.value('(RANGEHIGH)[1]','int'), -1) AS 'RANGE2HIGH',
T2.c.value('(ESTIMATEDWEALTHLOWID)[1]','uniqueidentifier') AS 'ESTIMATEDWEALTH2LOWID',
T2.c.value('(ESTIMATEDWEALTHHIGHID)[1]','uniqueidentifier') AS 'ESTIMATEDWEALTH2HIGHID',
T2.c.value('(MAJORGIVINGCAPACITYLOWID)[1]','uniqueidentifier') AS 'MAJORGIVINGCAPACITY2LOWID',
T2.c.value('(MAJORGIVINGCAPACITYHIGHID)[1]','uniqueidentifier') AS 'MAJORGIVINGCAPACITY2HIGHID',
T2.c.value('(TARGETGIFTRANGELOWID)[1]','uniqueidentifier') AS 'TARGETGIFTRANGE2LOWID',
T2.c.value('(TARGETGIFTRANGEHIGHID)[1]','uniqueidentifier') AS 'TARGETGIFTRANGE2HIGHID',
T2.c.value('(WEALTHESTIMATORRATINGLOWID)[1]','uniqueidentifier') AS 'WEALTHESTIMATORRATING2LOWID',
T2.c.value('(WEALTHESTIMATORRATINGHIGHID)[1]','uniqueidentifier') AS 'WEALTHESTIMATORRATING2HIGHID'
from
@SECONDARYSECTIONS1.nodes('/SECONDARYSECTIONS1/ITEM') T(c)
left join
@SECONDARYSECTIONS2.nodes('/SECONDARYSECTIONS2/ITEM') T2(c)
on
T.c.value('(SEQUENCE)[1]','int') = T2.c.value('(SEQUENCE)[1]','int')
for
xml raw('ITEM'),type,elements,root('SECONDARYSECTIONS'),BINARY BASE64
)
begin try
exec dbo.USP_PROSPECTSEGMENTATION_SECTIONS_SECONDARY_ADDFROMXML @SECONDARYVARIABLEID,
@SECONDARYSECTIONS,
@CHANGEAGENTID;
end try
begin catch
set @InfoMsg='BBERR_ORIGINAL_ERROR:50002';
raiserror (@InfoMsg,1,11);
select
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
raiserror(@ErrorMessage,@ErrorSeverity,@ErrorState);
return 1;
end catch
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;