spAddUpdate_EmailList_Subscription

Parameters

Parameter	Parameter Type	Mode
@PKID	int	INOUT
@EmailListID	int	IN
@UserID	int	IN
@HasOpted	bit	IN
@BackofficeSystemPeopleID	int	IN
@EmailAddress	nvarchar(255)	IN
@FirstName	nvarchar(255)	IN
@LastName	nvarchar(255)	IN
Definition

Copy

  CREATE PROCEDURE [dbo].[spAddUpdate_EmailList_Subscription]
          (
              @PKID int OUTPUT,
              @EmailListID    int,        
              @UserID int,
              @HasOpted bit,
              @BackofficeSystemPeopleID int,
              @EmailAddress nvarchar(255),
              @FirstName nvarchar(255),
              @LastName nvarchar(255)
          )
          AS

          declare @ID uniqueidentifier;
          declare @CHANGEAGENTID uniqueidentifier;
          declare @CURRENTDATE date;    

          exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
          set @CURRENTDATE = getdate();
          set @ID = newid()

          -- BBIS Bug 294839; Accounting for anonymous email subscribers subscribing more than once
          declare @emailExists bit;
          select @emailExists = case when exists(select * from [dbo].EmailList_Subscription where EmailListID = @EmailListID and EmailAddress = @EmailAddress)
          then 1 else 0 end;

          IF @emailExists <> 1
              BEGIN
              IF (@PKID<=0) BEGIN
                  INSERT INTO [dbo].EmailList_Subscription
                  (
                      EmailListID, 
                      UserID, 
                      HasOpted, 
                      BackofficeSystemPeopleID, 
                      EmailAddress, 
                      FirstName, 
                      LastName
                  )
                  VALUES
                  (
                      @EmailListID, 
                      @UserID, 
                      @HasOpted, 
                      @BackofficeSystemPeopleID, 
                      @EmailAddress, 
                      @FirstName, 
                      @LastName
                  )

                  SELECT @PKID = @@Identity

              END
              ELSE
              BEGIN
                  UPDATE [EmailList_Subscription] SET
                      EmailListID = @EmailListID, 
                      UserID = @UserID, 
                      HasOpted = @HasOpted, 
                      BackofficeSystemPeopleID = @BackofficeSystemPeopleID, 
                      EmailAddress = @EmailAddress, 
                      FirstName = @FirstName, 
                      LastName = @LastName
                  WHERE ID=@PKID
              END
          END

        -- SHL BBIS Bug 285685; Reset the global preference when someone opts into something new
        -- Done mainly so that repeat anonymous users (using the same email) can receive emails after subscribing to a newsletter instead of being permanently opted out
          exec dbo.spAddUpdate_EmailList_GlobalSubscription @EmailAddress, 1