Looping through stored procedure - email

Question

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 23-Mar-19 9:50am

AZ93

Updated 26-Mar-19 3:45am

Maciej Los

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Gerry Schmitz · Answer 1 · 2019-03-23T10:39:00

Solution 1

You need to track the addresses sent (A); then select the top 200 from B not in A.

Posted 23-Mar-19 10:39am

Gerry Schmitz

CHill60 · Answer 2 · 2019-03-26T03:46:00

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.