Stored proc sends email regardless of whether IsOpen is True or Not

Hello again experts, this is part an ongoing effort to get the stored procedure to work correctly.

I have One known issue I would like your assistance with.

1, The issue is that, when there are more than one records where IsOpen=1, I expect two emails to go out. Instead, only one record goes out eve though the two records are eventually updated with IsOpen being set 1 where they were previously NULL or 0.

I know that part of the reason is because of this:

select @email=email,...

If I change the code to this:

select @email=@email +';'+email,... then everyone on the list gets the same email and everyone sees everyone else's email which is not a good idea.

Any ideas how to resolve this?

Code is below and as usual, thanks for your help.

ALTER proc [dbo].[EmailUpdatedRecords] as   
 DECLARE MAIL_CURSOR CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
  
	 SELECT   [ID]
             ,[Department]
			 ,[Bldg]
			 ,[DateCreated]
			 ,[RequestID]
			 ,[Email]
			 ,[SubmittedBy]
	FROM FeedBack
    WHERE Status != 1
   
 Declare @mailid int 
 declare @dept nvarchar(200)
 declare @loc nvarchar(200)
 declare @dteCreated nvarchar(20)
 declare @RequestID nvarchar(12)
 declare @email nvarchar(200)
 declare @authorizedname nvarchar(200) 
 declare @message nvarchar(1000)
 OPEN MAIL_CURSOR
  
 FETCH MAIL_CURSOR into @mailid,@dept,@loc,@dteCreated,@RequestID,@email,@authorizedname
 
 WHILE @@FETCH_STATUS = 0
 BEGIN
 
 	 SELECT @email = email, @mailid=ID, @message = @message, @RequestID = RequestID,@authorizedname=SUBSTRING(SubmittedBy, CHARINDEX(',', SubmittedBy) + 1, LEN(SubmittedBy) - CHARINDEX(',', SubmittedBy) + 1)
		+ ' ' + SUBSTRING(SubmittedBy, 1, CHARINDEX(',', SubmittedBy) - 1),
	 @loc=Bldg, @dtecreated=DateCreated, @dept=Department
	 	FROM Feedback
WHERE email=@email
AND (lsOpen IS NULL or lsOpen = 0)

  select @message = 'This is a computer generated email message.
		    Please DO NOT use the REPLY button above to respond to this email.
		    
		    Dear '+ @authorizedname +':
		    
		    Thank you for using the order processing system.
		    
	            Please click the link below to complete a survey
		    
		    http://feedbackSurvey.php?rID=' +@RequestID+'&loc='+Replace(@loc,' ', '%20')+'&dept='+Replace(@dept,' ', '%20')+'
		    
		    Regards, 
           Order administrator.' 
--exec sp_send_cdontsmail @mail1, null,null,@content1,null
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Customer Feedback Survey', -- your defined email profile or static info
@recipients = @email, -- your email
--@blind_copy_recipients = @email,
@subject = 'Your feedback is needed',
@body = @message
--Update the record in ClosedRequest table where Sent = 'No'.

Update Feedback SET lsOpen = 1 WHERE lsOpen != 1 and ID = @mailid
 
 FETCH MAIL_CURSOR INTO @mailid,@dept,@loc,@dteCreated,@RequestID,@email,@authorizedname
 END
  
 CLOSE MAIL_CURSOR
 DEALLOCATE MAIL_CURSOR

Put print statements just before the sp_send_dbmail to see what you are handing over to sp_send_dbmail. Like this:

PRINT @email;
PRINT @message;
EXEC msdb.dbo.sp_send_dbmail
-- etc

If it is null then the mail wouldn't get sent.

Also, after your update statement, put a print statement to see how many rows were affected.

Update Feedback SET lsOpen = 1 WHERE lsOpen != 1 and ID = @mailid
print @@ROWCOUNT;

Hi James,

Sorry for late response. I didn't even know there was a response.

I usually get an email when there is a response to my question. This time, I didn't.

So, I did add
PRINT @email;
PRINT @message;

Two strange things are happening.

I did this a few times.

Assume that I have two records where IsOpen !=1, when I execute the stored proc, sometimes it shows that two messages were processed.

In each instance, only one email was sent.

Second, I added the PRINT @@ROWCOUNT but it shows 1 update when one record displays; two when two records are updated.

Why not just do blind copies?

Also note that in general, don't write SELECT @foo = @foo + ...

this is not supported (even though it often works). You can get incorrect results. Better to use SET instead of SELECT here or use FOR XML PATH('')

You need to cursor through the detail data, not the upper-level selection of emails. Something like below:

ALTER PROCEDURE [dbo].[EmailUpdatedRecords]
AS
SET NOCOUNT ON;

DECLARE MAIL_CURSOR CURSOR LOCAL FAST_FORWARD READ_ONLY FOR  
    SELECT  email, 
            ID, 
            message, 
            RequestID, 
            SUBSTRING(SubmittedBy, CHARINDEX(',', SubmittedBy) + 1, LEN(SubmittedBy) - CHARINDEX(',', SubmittedBy) + 1) + ' ' + SUBSTRING(SubmittedBy, 1, CHARINDEX(',', SubmittedBy) - 1) AS authorizedname,
	        Bldg, 
	        DateCreated, 
	        Department
	FROM   Feedback
	WHERE email IN (
            SELECT DISTINCT [Email]
	        FROM FeedBack
            WHERE Status != 1 AND Email > ''
          ) AND
         (lsOpen IS NULL or lsOpen = 0)
    ORDER BY email

declare @email nvarchar(200)
Declare @mailid int 
declare @message nvarchar(1000)
declare @RequestID nvarchar(12)
declare @authorizedname nvarchar(200) 
declare @bldg nvarchar(200)
declare @dateCreated nvarchar(20)
declare @department nvarchar(200)
 
OPEN MAIL_CURSOR
 
WHILE 1 = 1
BEGIN
    FETCH MAIL_CURSOR into @email, @mailid, @message, @RequestID, @authorizedname, @bldg, @dateCreated, @department
    
    IF @@FETCH_STATUS <> 0
        BREAK;
        
    SELECT 
        @mailid = ISNULL(@mailid, 0),
        @RequestID = ISNULL(@RequestID, 0),
        @authorizedname = ISNULL(@authorizedname, ''),
        @bldg = ISNULL(@bldg, ''),
        @dateCreated = ISNULL(@dateCreated, 0),
        @department = ISNULL(@department, '')  

    select @message = 'This is a computer generated email message.
		    Please DO NOT use the REPLY button above to respond to this email.
		    
		    Dear '+ @authorizedname +':
		    
		    Thank you for using the order processing system.
		    
	            Please click the link below to complete a survey
		    
		    http://feedbackSurvey.php?rID=' +@RequestID+'&loc='+Replace(@bldg,' ', '%20')+'&dept='+Replace(@department,' ', '%20')+'
		    
		    Regards, 
           Order administrator.'         

    --exec sp_send_cdontsmail @mail1, null,null,@content1,null
    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'Customer Feedback Survey', -- your defined email profile or static info
        @recipients = @email, -- your email
        --@blind_copy_recipients = @email,
        @subject = 'Your feedback is needed',
        @body = @message

    Update Feedback 
    SET lsOpen = 1 
    WHERE ID = @mailid AND (lsOpen IS NULL OR lsOpen != 1)

END

DEALLOCATE MAIL_CURSOR
2 Likes

Awesome, Scott.

Thanks so much - works great now.