Click here to Skip to main content
15,918,516 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am writing a Stored procedure that will send email alerts to users. You can only send 200 users an email at one time. I have more than 1000 users to send email to so I need to loop through the table and get 200 users at a time. How do I do this looping in sql.

I already have the email part working, I just need help with looping 200 users each time until it comes to an end.


1. The SP will call the table and collect all the users for a certain facility, then we need to send emails to all these users and the sp will go through all the users.

What I have tried:

SQL
CREATE PROCEDURE [dbo].[sp_emailAlertJob]
(
	@EMAILDESC INT = 1,
	@FACILITY VARCHAR(5) = 'TEST' 
)
--WITH ENCRYPTION
AS
BEGIN

DECLARE @SENDTO VARCHAR(4000)

DECLARE @Getusers CURSOR



	BEGIN
				
		SET @Getusers = CURSOR FOR
		SELECT TOP (2) EMAIL
		FROM [dbo].[Employee]
		WHERE FACILITY = @FACILITY  
		ORDER BY  TSG_RECORD_ID DESC

	END

		OPEN @Getusers
		FETCH NEXT FROM @Getusers INTO @SENDTO

			WHILE @@FETCH_STATUS = 0
			BEGIN
				BEGIN
					EXEC [sp_EmailAlert] @SENDTO,@EMAILDESC,@FACILITY
				END

			FETCH NEXT FROM @Getusers INTO @SENDTO

			END
		
		CLOSE @Getusers
		DEALLOCATE @Getusers

END
Posted
Updated 26-Mar-19 3:45am
v2

You need to track the addresses sent (A); then select the top 200 from B not in A.
 
Share this answer
 
I hate cursors and it is possible to do this without one.

Create a temporary table to log your progress and update it as you send the emails out. E.g.
SQL
DECLARE @SENDTOLIST TABLE (id int identity(1,1), sendto VARCHAR(4000), done bit)

INSERT INTO @SENDTOLIST(sendto, done)
SELECT EMAIL, 0 FROM @Employee WHERE FACILITY = @FACILITY  ORDER BY TSG_RECORD_ID
Then just use two loops - the outer one ensures you cover every item to be emailed and the inner one does the 200 emails
SQL
DECLARE @SENDTO VARCHAR(4000)
DECLARE @id INT
-- Loop until all items have been sent
WHILE EXISTS(SELECT TOP 1 done FROM @SENDTOLIST WHERE done = 0)
BEGIN
	-- Process 200 emails
	print 'Sending 200 emails'
	DECLARE @i INT = 1
	WHILE @i <= 200
	BEGIN
		SELECT TOP 1 @SENDTO=sendto, @id = id FROM @SENDTOLIST WHERE done = 0
		EXEC [sp_EmailAlert] @SENDTO,@EMAILDESC,@FACILITY
		UPDATE @SENDTOLIST SET done = 1 WHERE id = @id
		SET @i = @i + 1
	END
	-- Whatever you have to do to wait for the next slot for 200 emails
	print 'waiting for next slot'
END
Things to note:
The outer WHILE depends on there being a minimum of 1 item left to process - that TOP 1 is not a typing error. This covers off the number of emails not being an exact multiple of 200.
Similarly, because I'm using a loop and not a cursor, I only want to read one record at a time. So that second TOP 1 is also not a typing error.
 
Share this answer
 

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