USP_DATAFORMTEMPLATE_ADD_ANONYMOUSEMAILPREFERENCE_BULK

The save procedure used by the add dataform template "Anonymous Email Preference Add Data 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.
@ANONYMOUSEMAILSUBSCRIBERID	uniqueidentifier	IN	Input parameter indicating the context ID for the record being added.
@PREFERENCES	xml	IN	Preferences

Definition

Copy



CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_ANONYMOUSEMAILPREFERENCE_BULK
(
  @ID uniqueidentifier = null output,
  @CHANGEAGENTID uniqueidentifier = null,
  @ANONYMOUSEMAILSUBSCRIBERID uniqueidentifier,
  @PREFERENCES xml
)
as

set nocount on;

if @ID is null
    set @ID = newid()

if @CHANGEAGENTID is null  
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

declare @CURRENTDATE datetime
set @CURRENTDATE = getdate()

begin try

--Pull the new prefs out into a table variable

declare @PREFSTABLE table(APPEALCATEGORYCODEID uniqueidentifier, SITEID uniqueidentifier, BUSINESSUNITCODEID uniqueidentifier, OPTEDOUT bit)
insert into @PREFSTABLE
select 
P.I.value('APPEALCATEGORYCODEID[1]', 'uniqueidentifier'),
P.I.value('SITEID[1]', 'uniqueidentifier'),
P.I.value('BUSINESSUNITCODEID[1]', 'uniqueidentifier'),
P.I.value('OPTEDOUT[1]', 'bit')
from @PREFERENCES.nodes('/PREFERENCES/ITEM') P(I)

--Update changes to existing preferences

update dbo.ANONYMOUSEMAILPREFERENCE
set OPTEDOUT = P.OPTEDOUT
from dbo.ANONYMOUSEMAILPREFERENCE A
inner join @PREFSTABLE P on
A.ANONYMOUSEMAILSUBSCRIBERID = @ANONYMOUSEMAILSUBSCRIBERID
AND ISNULL(P.BUSINESSUNITCODEID, '00000000-0000-0000-0000-000000000000') = ISNULL(A.BUSINESSUNITCODEID, '00000000-0000-0000-0000-000000000000')
AND ISNULL(P.APPEALCATEGORYCODEID, '00000000-0000-0000-0000-000000000000') = ISNULL(A.APPEALCATEGORYCODEID, '00000000-0000-0000-0000-000000000000')
AND ISNULL(P.SITEID, '00000000-0000-0000-0000-000000000000') = ISNULL(A.SITEID, '00000000-0000-0000-0000-000000000000')

--Add new preferences 

insert dbo.ANONYMOUSEMAILPREFERENCE (ANONYMOUSEMAILSUBSCRIBERID, APPEALCATEGORYCODEID, BUSINESSUNITCODEID, SITEID, OPTEDOUT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select @ANONYMOUSEMAILSUBSCRIBERID, P.APPEALCATEGORYCODEID, P.BUSINESSUNITCODEID, P.SITEID, P.OPTEDOUT, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
from @PREFSTABLE P
left outer join dbo.ANONYMOUSEMAILPREFERENCE A on
A.ANONYMOUSEMAILSUBSCRIBERID = @ANONYMOUSEMAILSUBSCRIBERID
AND ISNULL(P.BUSINESSUNITCODEID, '00000000-0000-0000-0000-000000000000') = ISNULL(A.BUSINESSUNITCODEID, '00000000-0000-0000-0000-000000000000')
AND ISNULL(P.APPEALCATEGORYCODEID, '00000000-0000-0000-0000-000000000000') = ISNULL(A.APPEALCATEGORYCODEID, '00000000-0000-0000-0000-000000000000')
AND ISNULL(P.SITEID, '00000000-0000-0000-0000-000000000000') = ISNULL(A.SITEID, '00000000-0000-0000-0000-000000000000')
where A.ID is null

end try

begin catch
    exec dbo.USP_RAISE_ERROR
    return 1
end catch

return 0