时间戳

首页 > 解决方案 > 关于在 SQL Server 中发送自动批量邮件的问题

sql-server - 关于在 SQL Server 中发送自动批量邮件的问题

问题描述

我在 SQL Server 中有一个数据库，其中有一个user包含 3 列的表：姓名、出生日期和电子邮件。

我有一个存储过程，它通过这个表查找今天谁过生日，并向他发送一封祝贺电子邮件，到目前为止它的工作时间为 100。

但是，我想在我向数据库的所有用户（生日除外）发送电子邮件的同一过程中添加，通知他们 x 人今天过生日。

这是我当前的 SQL Server 代码：

CREATE PROCEDURE spSendEmail
AS
    DECLARE @email nvarchar(128)
    DECLARE @name  nvarchar(128)
    DECLARE @Date  date

    SELECT 
        GETDATE(), 
        MONTH(GETDATE()), DAY(GETDATE()), YEAR(GETDATE())

    DECLARE email_cursor CURSOR FOR 
        SELECT u.name, u.email
        FROM dbo.users u
        WHERE MONTH(u.dateofbirth) = MONTH(GETDATE())
          AND DAY(u.dateofbirth) = DAY(GETDATE()) 

    OPEN email_cursor

    FETCH NEXT FROM email_cursor INTO @name, @email

    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT @email

        DECLARE @subject nvarchar(255)
        DECLARE @Bodytext nvarchar(512)

        SET @BodyText = @Name + '' + 'we wish you happy birthday'
        SET @Subject = 'Happy Birthday' 

        EXEC msdb.dbo.sp_send_dbmail
                      @profile_name = 'Birthday Alert',
                      @recipients = @email,
                      @body = @Bodytext,
                      @subject = @subject;

        FETCH NEXT FROM email_cursor INTO @name, @email
    END 

    CLOSE email_cursor
    DEALLOCATE email_cursor

标签： sql-serverstored-proceduressql-server-2017

解决方案

尝试这个：

CREATE PROCEDURE spSendEmail
AS
BEGIN
    DECLARE @email nvarchar(128)
    DECLARE @name  nvarchar(128)
    DECLARE @has_birthday BIT
    DECLARE @Date  date
    DECLARE @birthday_boys INT -- update this to 

    SELECT 
        GETDATE(), 
        MONTH(GETDATE()), DAY(GETDATE()), YEAR(GETDATE())

    IF(OBJECT_ID('tempdb..users') IS NOT NULL)
        DROP TABLE #users;

    -- For Sql Server version >= 2016: DROP TABLE IF EXISTS #users

    SELECT u.name, u.email
        , IIF(MONTH(u.dateofbirth) = MONTH(GETDATE()) AND DAY(u.dateofbirth) = DAY(GETDATE()),1,0) has_birthday 
    INTO #users
    FROM dbo.users u

    --Get all birthday boys list

    SELECT  @birthday_boys = COUNT(u.name)
    FROM #users u
    WHERE has_birthday = 1

    DECLARE email_cursor CURSOR FOR 
        SELECT u.name, u.email, u.has_birthday
        FROM #users
    OPEN email_cursor

    FETCH NEXT FROM email_cursor INTO @name, @email, @has_birthday

    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT @email

        DECLARE @subject nvarchar(255)
        DECLARE @Bodytext nvarchar(512)

        SET @BodyText = IIF(@has_birthday = 1, @Name + '' + 'we wish you happy birthday', CONCAT('Birthday boys'' count is ', @birthday_boys))  
        SET @Subject = IIF(@has_birthday = 1, 'Happy Birthday', 'Birthday Boys'' Count') 

        EXEC msdb.dbo.sp_send_dbmail
                      @profile_name = 'Birthday Alert',
                      @recipients = @email,
                      @body = @BodyText,
                      @subject = @subject


        FETCH NEXT FROM email_cursor INTO @name, @email
    END 

    CLOSE email_cursor
    DEALLOCATE email_cursor

    IF(OBJECT_ID('tempdb..users') IS NOT NULL)
        DROP TABLE #users;
    -- For Sql Server version >= 2016: DROP TABLE IF EXISTS #users

END

推荐阅读