Click here to Skip to main content
15,891,607 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I am using the awesome built procedure for sending emails to all users of a table..In this table I have two columns namely Ename and Ecount. If Ecount reaches greater than 3, then the procedure must be stopped or exit. For example the Ecount == 3, then this user will never receive message.

XML
DECLARE 
@id nvarchar(MAX),
@Counter int,
@CheckCount int
DECLARE CC1 CURSOR READ_ONLY
FOR
SELECT Ename, Ecount
FROM TestDB.dbo.TestEmail
OPEN CC1
FETCH NEXT FROM CC1 INTO 
@id, @Counter
WHILE @@FETCH_STATUS = 0
BEGIN

	Begin
	EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Boom',
    @recipients = @id,
    @body = 'The stored procedure finished successfully. #$%',
    @subject = 'Automated Success Message' ;
	update TestDB.dbo.TestEmail set Ecount=Ecount+1 where Ename=@id 
	print @id
	END
    Select @CheckCount= Ecount from TestDB.dbo.TestEmail where Ename=@id
    print @CheckCount
    If @CheckCount > 3
	Begin
  	exec msdb.dbo.sp_update_job @job_id = N'6a81d6b3-af2d-4a27-b688-b9c69098f840', @enabled = 0
	return
	PRINT 'Count reached 3+'
	End 
	FETCH NEXT FROM CC1 INTO 
	@id, @Counter
END
CLOSE CC1
DEALLOCATE CC1


Currently, I am not receiving any email whatsoever.. but earlier I was receiving emails every single time I hit execute this script when I used my email explicitly instead of @id. Please tell me where I am mistaking..
Posted
Updated 13-Jul-14 22:50pm
v2
Comments
_Asif_ 14-Jul-14 4:50am    
Where have you initialize @id ? What value will it pass to the SP?
JBobby 14-Jul-14 5:27am    
@Id will pass Ename (emails) to sp. I thought the cursor is initializing ID with emails.

1 solution

Hi,

You are putting Ename column value in @id variable whereas you have to put emailID column value in @id. Please check and confirm.
 
Share this answer
 
Comments
JBobby 14-Jul-14 5:29am    
Actually Ename is emailid column bro.. Ename has emails IDS.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900