如何在SQL Server中编写存储过程实现订阅到期前2个月自动邮件通知

嘿，这个需求我帮不少人实现过，分几步就能搞定，我给你详细拆解：

1. 编写核心存储过程 #

首先我们需要创建一个存储过程，用来筛选出未来两个月内到期的订阅用户，然后给他们发送提醒邮件。这里假设你的订阅表名为Subscriptions，包含start-date、expiry-date，以及用户的邮箱user_email、用户名user_name字段（如果字段名不一样，你自己替换就行）。

CREATE PROCEDURE dbo.SendSubscriptionExpiryReminders
AS
BEGIN
    SET NOCOUNT ON;

    -- 定义提醒时间范围：当前日期到未来2个月的日期
    DECLARE @CurrentDate DATE = GETDATE();
    DECLARE @TwoMonthsLater DATE = DATEADD(MONTH, 2, @CurrentDate);

    -- 声明变量存储用户信息
    DECLARE @UserEmail NVARCHAR(100), @UserName NVARCHAR(100), @ExpiryDate DATE;

    -- 用游标遍历符合条件的用户（如果数据量极大，也可以用批量处理优化）
    DECLARE UserReminderCursor CURSOR FOR
        SELECT user_email, user_name, [expiry-date]
        FROM dbo.Subscriptions
        -- 筛选出未来2个月内到期的订阅
        WHERE [expiry-date] BETWEEN @CurrentDate AND @TwoMonthsLater
          -- 可选：加个判断避免重复发送（建议新增last_reminder_date字段）
          -- AND (last_reminder_date IS NULL OR last_reminder_date < DATEADD(DAY, -7, @CurrentDate))

    OPEN UserReminderCursor;
    FETCH NEXT FROM UserReminderCursor INTO @UserEmail, @UserName, @ExpiryDate;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 调用SQL Server的邮件存储过程发送提醒
        EXEC msdb.dbo.sp_send_dbmail
            @profile_name = 'YourMailProfile', -- 替换成你自己的Database Mail配置文件名
            @recipients = @UserEmail,
            @subject = '您的订阅即将到期，请及时续费',
            @body = '亲爱的' + @UserName + '：' + CHAR(13) + CHAR(10) +
                    '您好！您的订阅将于' + CONVERT(NVARCHAR(20), @ExpiryDate, 23) + '到期。' + CHAR(13) + CHAR(10) +
                    '请尽快完成续费，以免影响您的服务使用。' + CHAR(13) + CHAR(10) +
                    '感谢您的支持！',
            @body_format = 'TEXT'; -- 如果要HTML格式，改成'HTML'即可

        -- 可选：更新已发送标记，避免重复提醒
        -- UPDATE dbo.Subscriptions
        -- SET last_reminder_date = @CurrentDate
        -- WHERE user_email = @UserEmail AND [expiry-date] = @ExpiryDate;

        FETCH NEXT FROM UserReminderCursor INTO @UserEmail, @UserName, @ExpiryDate;
    END

    CLOSE UserReminderCursor;
    DEALLOCATE UserReminderCursor;
END
GO

2. 前置准备：配置Database Mail #

存储过程依赖SQL Server的Database Mail功能，所以得先配置好：

• 打开SQL Server Management Studio（SSMS），展开左侧的管理 -> Database Mail
• 跟着向导创建一个邮件配置文件，填写你的SMTP服务器信息（比如企业邮箱的SMTP地址、端口、账号密码）
• 给执行存储过程的数据库用户授予msdb数据库的DatabaseMailUserRole角色，确保它有权限发送邮件

3. 自动化调度：用SQL Server Agent定时执行 #

要实现自动定期发送，我们需要把存储过程加到SQL Server Agent作业里：

• 展开左侧的SQL Server Agent -> 作业，右键选择新建作业
• 常规页：给作业起个名字，比如「订阅到期提醒每日任务」
• 步骤页：新建一个步骤，类型选Transact-SQL脚本(TSQL)，选择你的业务数据库，命令里写EXEC dbo.SendSubscriptionExpiryReminders;
• 调度页：新建调度，设置执行频率（比如每天凌晨2点执行一次，避开业务高峰），保存即可

几个关键注意事项 #

• 日期计算细节：DATEADD(MONTH, 2, GETDATE())会自动处理月末的特殊情况（比如3月31日加2个月会得到5月31日），不用额外担心
• 避免重复提醒：强烈建议在Subscriptions表新增一个last_reminder_date字段，每次发送邮件后更新这个字段，避免短时间内重复给同一个用户发提醒
• 美化邮件内容：如果想发更美观的HTML邮件，可以把@body改成HTML格式，比如：

  @body = '<html><body>
            <p>亲爱的' + @UserName + '：</p>
            <p>您好！您的订阅将于<strong>' + CONVERT(NVARCHAR(20), @ExpiryDate, 23) + '</strong>到期，请尽快续费。</p>
            <p>感谢您的支持！</p>
          </body></html>',
  @body_format = 'HTML'
  

• 权限检查：确保SQL Server Agent的代理账户有访问Subscriptions表的权限，以及执行sp_send_dbmail的权限

内容的提问来源于stack exchange，提问作者Anil