USP_BBNC_ADDPHONEANDEMAILTOACKNOWLEDGEE

Add an email address and phone number to a newly created tribute acknowledgee.

Parameters

Parameter	Parameter Type	Mode
@CONSTITUENTID	uniqueidentifier	IN
@PHONENUMBER	nvarchar(100)	IN
@EMAILADDRESS	UDT_EMAILADDRESS	IN
@CHANGEAGENTID	uniqueidentifier	IN
@CHANGEDATE	datetime	IN

Definition

Copy



            create procedure dbo.USP_BBNC_ADDPHONEANDEMAILTOACKNOWLEDGEE
            (
                @CONSTITUENTID uniqueidentifier,
                @PHONENUMBER nvarchar(100),
                @EMAILADDRESS dbo.UDT_EMAILADDRESS,
                @CHANGEAGENTID uniqueidentifier,
                @CHANGEDATE datetime
            )
            as
            set nocount on;

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

            if @CHANGEDATE is null
                set @CHANGEDATE = getdate();

            declare @SEQUENCE int;

            if not @CONSTITUENTID is null
            begin
                if len(coalesce(@PHONENUMBER, '')) > 0
                begin
                    if (select count(ID) from dbo.PHONE where CONSTITUENTID = @CONSTITUENTID and NUMBER = @PHONENUMBER) = 0
                    begin
                        select @SEQUENCE = coalesce(max(SEQUENCE), 0) + 1 from dbo.PHONE where CONSTITUENTID = @CONSTITUENTID;
                        insert into dbo.PHONE(CONSTITUENTID, NUMBER, ISPRIMARY, SEQUENCE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                            values(@CONSTITUENTID, @PHONENUMBER, 0, @SEQUENCE, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);
                    end
                end

                if len(coalesce(@EMAILADDRESS, '')) > 0
                begin
                    if (select count(ID) from dbo.EMAILADDRESS where CONSTITUENTID = @CONSTITUENTID and EMAILADDRESS = @EMAILADDRESS) = 0
                    begin
                        select @SEQUENCE = coalesce(max(SEQUENCE), 0) + 1 from dbo.EMAILADDRESS where CONSTITUENTID = @CONSTITUENTID;
                        insert into dbo.EMAILADDRESS(CONSTITUENTID, EMAILADDRESS, ISPRIMARY, SEQUENCE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                            values(@CONSTITUENTID, @EMAILADDRESS, 0, @SEQUENCE, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);
                    end
                end
            end