USP_FAFNOTIFICATION_SUBSCRIPTION_ADDUPDATE

create procedure dbo.USP_FAFNOTIFICATION_SUBSCRIPTION_ADDUPDATE
(
  @CONSTITUENTID uniqueidentifier, 
    @EVENTID uniqueidentifier,
    @ROLECODE tinyint -- 0: Company leader; 1: team leader; 3: household leader

)
as
begin
    declare @fafroles nvarchar(50) = 'individual'  
  declare @userID int

  if @ROLECODE < 2 
    set @fafroles = @fafroles + ',team leader'
  else if @ROLECODE = 3
    set @fafroles = @fafroles + ',household leader'

  /* take this approach since a user can own multiple logins after a constituent merge action */

  delete from dbo.NotificationSubscription where 
        UserID in (select CLIENTUSERID from dbo.UFN_CLIENTUSERS_GETIDLISTBYCONSTITUENTID(@CONSTITUENTID))
        and NotificationID in (select NCNOTIFICATIONID from dbo.EVENTEMAILTEMPLATE where EVENTID = @EVENTID)

  declare UserIDsCursor cursor local fast_forward for
      select CLIENTUSERID from dbo.UFN_CLIENTUSERS_GETIDLISTBYCONSTITUENTID(@CONSTITUENTID)

  open UserIDsCursor fetch next from UserIDsCursor into @userID
  while @@fetch_status = 0
  begin                                    
    exec dbo.USP_FAFNOTIFICATION_SUBSCRIPTION @UserID=@userID, @EVENTID=@EVENTID, @FAFRoles=@fafroles 

    fetch next from UserIDsCursor into @userID
  end        
  close UserIDsCursor
  deallocate UserIDsCursor   

end