USP_DATAFORMTEMPLATE_EDIT_DESIGNATIONCAMPAIGNADDDESIGNATIONSELECTION
The save procedure used by the edit dataform template "Designation Campaign Add Multiple Designations".
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | The input ID parameter indicating the ID of the record being edited. |
| @DESIGNATIONS | xml | IN | Designations |
| @CHANGEAGENTID | uniqueidentifier | IN | Input parameter indicating the ID of the change agent invoking the procedure. |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_DESIGNATIONCAMPAIGNADDDESIGNATIONSELECTION (
@ID uniqueidentifier,
@DESIGNATIONS xml,
@CHANGEAGENTID uniqueidentifier = null
) as begin
set nocount on;
begin try
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
declare @CURRENTDATE datetime;
set @CURRENTDATE = getdate();
declare @DESIGNATIONSTOADD table (
[CAMPAIGNSUBPRIORITYID] uniqueidentifier,
[DATEFROM] datetime,
[DATETO] datetime,
[DESIGNATIONID] uniqueidentifier,
[ID] uniqueidentifier,
[SEQUENCE] int)
insert into @DESIGNATIONSTOADD select
[CAMPAIGNSUBPRIORITYID],
[DATEFROM],
[DATETO],
[DESIGNATIONID],
[ID],
[SEQUENCE]
from dbo.UFN_CAMPAIGN_DESIGNATIONS_FROMITEMLISTXML(@DESIGNATIONS)
if exists
(
select
DESIGNATIONCAMPAIGN.ID
from
dbo.DESIGNATIONCAMPAIGN
inner join @DESIGNATIONSTOADD DESIGNATIONSTOADD on DESIGNATIONCAMPAIGN.DESIGNATIONID = DESIGNATIONSTOADD.DESIGNATIONID
where
DESIGNATIONCAMPAIGN.CAMPAIGNID = @ID and
(
(DESIGNATIONSTOADD.DATEFROM is null and DESIGNATIONSTOADD.DATETO is null) or
(DESIGNATIONCAMPAIGN.DATEFROM is null and DESIGNATIONCAMPAIGN.DATETO is null) or
(DESIGNATIONCAMPAIGN.DATEFROM is null and (DESIGNATIONSTOADD.DATEFROM is null or DESIGNATIONSTOADD.DATEFROM <= DESIGNATIONCAMPAIGN.DATETO)) or
(DESIGNATIONCAMPAIGN.DATETO is null and (DESIGNATIONSTOADD.DATETO is null or DESIGNATIONSTOADD.DATETO >= DESIGNATIONCAMPAIGN.DATEFROM)) or
(DESIGNATIONSTOADD.DATEFROM is null and DESIGNATIONCAMPAIGN.DATEFROM <= DESIGNATIONSTOADD.DATETO) or
(DESIGNATIONSTOADD.DATETO is null and DESIGNATIONCAMPAIGN.DATETO >= DESIGNATIONSTOADD.DATEFROM) or
(DESIGNATIONCAMPAIGN.DATETO between DESIGNATIONSTOADD.DATEFROM and DESIGNATIONSTOADD.DATETO) or
(DESIGNATIONCAMPAIGN.DATEFROM between DESIGNATIONSTOADD.DATEFROM and DESIGNATIONSTOADD.DATETO)
)
)
raiserror('OVERLAPPINGDESIGNATION', 13, 1);
exec dbo.USP_CAMPAIGN_DESIGNATIONS_ADDFROMXML @ID, @DESIGNATIONS, @CHANGEAGENTID, @CURRENTDATE;
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;
end