从SQL Server为数据集的每一行发送电子邮件 [英] Send email from SQL server for each row of a dataset

问题描述

我已经建立了一个存储过程，用于每周向一组员工发送电子邮件提醒。 SQL服务器代理每周运行一个计划的过程，构建这个周的员工数据集，然后我需要让每个员工都收到一封电子邮件，但是我不能使用与排定的SELECT语句一起存储的电子邮件。

I have built a stored procedure for sending an email reminder to a set of employees each week. The SQL server agent runs a scheduled procedure each week that builds this weeks dataset of employees and then I need to have each of the employees receive an email but I cannot use the email stored proc inline with the scheduled SELECT statement.

这可能是使用一个函数，但我使用EXEC msdb.dbo.sp_send_dbmail发送不能在函数中执行的邮件。

This would be possible using a function but I am using EXEC msdb.dbo.sp_send_dbmail to send mail which cannot be executed in a function.

推荐答案

使用存储过程。存储过程中使用光标来获取电子邮件的详细信息以及您要发送的内容。重复调用sp_send_dbmail，直到所有员工都收到一封电子邮件。

Use a stored procedure. Inside the stored procedure use a cursor to get the details of the email and what you want to send. Call the sp_send_dbmail repeatedly until all employees get an email.

好的，我花了几分钟时间设置了一个测试用例数据库。我们正在向Scott Adams，Dave Letterman和Bill Gates发送电子邮件。

Okay, it took me a few minutes to set you up a test case database. We are sending emails to Scott Adams, Dave Letterman and Bill Gates.

/*  
    Setup test database.
*/

-- Use master
USE [master]
GO

-- Create a simple database using models attributes
CREATE DATABASE [MAIL];
GO

-- Use mail
USE [MAIL]
GO

-- Drop existing
IF OBJECT_ID(N'[DBO].[EMAIL_LIST]') > 0
DROP TABLE [DBO].[EMAIL_LIST]
GO

-- Create new
CREATE TABLE [DBO].[EMAIL_LIST]
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    EMAIL_ADDRESS VARCHAR(64),
    EMAIL_SUBJ VARCHAR(64),
    EMAIL_BODY VARCHAR(256),
    SENT_FLAG TINYINT DEFAULT (0)
);

-- Insert simple data
INSERT INTO [DBO].[EMAIL_LIST] (EMAIL_ADDRESS, EMAIL_SUBJ, EMAIL_BODY)
VALUES
('[e-mail]','Dilbert','What''s up scott?'),
('[e-mail]','Late Show','Please read this letter Dave.'),
('[e-mail]','Gates','How''s the weather in Seatle?');

-- Show the data
SELECT * FROM [DBO].[EMAIL_LIST];

此存储过程读取未发送电子邮件的电子邮件列表，并发送电子邮件。

This stored procedure reads the email list of unsent emails and sends out the emails.

/*  
    Create stored procedure
*/

-- Drop existing
IF OBJECT_ID(N'[DBO].[SEND_EMAILS]') > 0
DROP PROCEDURE [DBO].[SEND_EMAILS]
GO

-- Create new
CREATE PROCEDURE [dbo].[SEND_EMAILS]
AS
BEGIN

    -- Error handling variables
    DECLARE @err_number int;
    DECLARE @err_line int;
    DECLARE @err_message varchar(2048);
    DECLARE @err_procedure varchar(2048);

    -- ** Error Handling - Start Try **
    BEGIN TRY

    -- No counting of rows
    SET NOCOUNT ON;

    -- Declare variables
    DECLARE @VAR_ADDRESS VARCHAR(64);
    DECLARE @VAR_SUBJ VARCHAR(64);
    DECLARE @VAR_BODY varchar(256);

    -- Get email list
    DECLARE VAR_CURSOR CURSOR FOR
        SELECT EMAIL_ADDRESS, EMAIL_SUBJ, EMAIL_BODY
        FROM [DBO].[EMAIL_LIST] 
        WHERE SENT_FLAG = 0;

    -- Open cursor
    OPEN VAR_CURSOR;

    -- Get first row
    FETCH NEXT FROM VAR_CURSOR 
        INTO @VAR_ADDRESS, @VAR_SUBJ, @VAR_BODY;

    -- While there is data
    WHILE (@@fetch_status = 0)
    BEGIN
        -- Send the email
        EXEC msdb.dbo.sp_send_dbmail 
            @recipients = @VAR_ADDRESS,
            @subject = @VAR_SUBJ,
            @body = @VAR_BODY,
            @body_format = 'HTML' ;  

        -- Grab the next record
        FETCH NEXT FROM VAR_CURSOR 
            INTO @VAR_ADDRESS, @VAR_SUBJ, @VAR_BODY;
    END

    -- Close cursor
    CLOSE VAR_CURSOR;

    -- Release memory
    DEALLOCATE VAR_CURSOR;            

    -- Update the table as processed
    UPDATE [DBO].[EMAIL_LIST] 
    SET SENT_FLAG = 1
    WHERE SENT_FLAG = 0;

    -- ** Error Handling - End Try **
    END TRY

    -- ** Error Handling - Begin Catch **
    BEGIN CATCH

      -- Grab variables 
      SELECT 
          @err_number = ERROR_NUMBER(), 
          @err_procedure = ERROR_PROCEDURE(),
          @err_line = ERROR_LINE(), 
          @err_message = ERROR_MESSAGE();

      -- Raise error
      RAISERROR ('An error occurred within a user transaction. 
                  Error Number        : %d
                  Error Message       : %s  
                  Affected Procedure  : %s
                  Affected Line Number: %d'
                  , 16, 1
                  , @err_number, @err_message, @err_procedure, @err_line);       

    -- ** Error Handling - End Catch **    
    END CATCH                

END

查看MSDB。[dbo]。[sysmail_mailitems]表，我们可以看到项目排队等待。这取决于使用公共默认配置文件设置的数据库邮件。

Looking at the MSDB.[dbo].[sysmail_mailitems] table, we can see the items were queued to go. This depends upon database mail being set up with a public default profile.

通过作业计划存储过程取决于您。

Scheduling the stored procedure via a job is up to you.

这篇关于从SQL Server为数据集的每一行发送电子邮件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！