循环存储过程 - 电子邮件 [英] Looping through stored procedure - email

问题描述

我正在编写一个存储过程，该过程将向用户发送电子邮件警报。您一次只能向200位用户发送一封电子邮件。我有超过1000个用户发送电子邮件，所以我需要遍历表格，一次获得200个用户。我如何在sql中执行此循环。



我已经有了电子邮件部分工作，我只需要帮助每次循环200个用户，直到它结束。





1. SP将调用该表并收集某个设施的所有用户，然后我们需要向所有用户发送电子邮件这些用户和sp将通过所有用户。



我尝试过：

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:

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

推荐答案

您需要跟踪发送的地址（A）;然后从B中选择前200但不在A中。

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

我讨厌游标，没有游标就可以这样做。



创建临时表以记录进度并在发送电子邮件时进行更新。例如

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.

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

然后只使用两个循环 - 外部循环确保您覆盖每个要通过电子邮件发送的项目，内部人员会发送200封电子邮件

Then just use two loops - the outer one ensures you cover every item to be emailed and the inner one does the 200 emails

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

注意事项：

外部WHILE取决于最小值剩下要处理的1个项目 - TOP 1 不是输入错误。这包括不是200的精确倍数的电子邮件数量。

同样，因为我使用循环而不是光标，我只想一次读取一条记录。所以第二个 TOP 1 也不是打字错误。

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.

这篇关于循环存储过程 - 电子邮件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！