在ＳＱＬ　ＳＥＲＶＥＲ中发送邮件的代码小例

最新推荐文章于 2025-12-02 15:55:42 发布

原创最新推荐文章于 2025-12-02 15:55:42 发布 · 799 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#user #sql server #存储 #null #测试

DataBase 专栏收录该内容

22 篇文章

订阅专栏

本文介绍了一个用于SQL Server的存储过程，该过程根据指定年份和季度从TBL_USER_INFO表中选择收件人，并构造邮件正文。文章详细展示了如何设置邮件参数、构建收件人列表及邮件内容。

--下面的代码根据目前项目中的所用到的存储过程改写而成.

ALTER PROCEDURE [dbo].[MAILSENDING_PROC_01]
    @YEAR VARCHAR(4),
    @SEASON VARCHAR(1)
AS
BEGIN
    DECLARE @USER VARCHAR(8)
    DECLARE @varRecipient varchar(1000)
    DECLARE @varSubject varchar(100)
    DECLARE @varCc_recipient varchar(100)
    DECLARE @varBcc_recipient varchar(100)
    DECLARE @varMessage varchar(8000)
    DECLARE @FIRST_BEGIN_YYYYMMDD VARCHAR(8)
    DECLARE @FIRST_END_YYYYMMDD VARCHAR(8)
    DECLARE @SECOND_BEGIN_YYYYMMDD VARCHAR(8)
    DECLARE @SECOND_END_YYYYMMDD VARCHAR(8)
    DECLARE @PARAMETER1 VARCHAR(5)
    DECLARE @PARAMETER2 VARCHAR(2)
    DECLARE @PARAMETER3 VARCHAR(80)
    DECLARE @USER_NO1 VARCHAR(8)
    DECLARE @USER_NO2 VARCHAR(8)

    set @varRecipient = ''
    --the recipitent
    DECLARE CUR_RECIPIENTS CURSOR FOR
        SELECT
            DISTINCT USER_NO1
        FROM
            TBL_USER_INFO
        WHERE
            USER_NO1 IS NOT NULL AND
            USER_NO1 <> '0' AND
            YEAR = @YEAR AND
            SEASON = @SEASON
        UNION
        SELECT
            DISTINCT USER_NO2
        FROM
            TBL_USER_INFO
        WHERE
            USER_NO2 IS NOT NULL AND
            USER_NO2 <> '0' AND
            YEAR = @YEAR AND
            SEASON = @SEASON

    open CUR_RECIPIENTS
    fetch next from CUR_RECIPIENTS into @USER
        while @@fetch_status = 0 begin
            if @varRecipient = '' begin
                SET @varRecipient = @USER
            end
            else begin
                SET @varRecipient = @varRecipient + ';' + @USER
            end

            fetch next from CUR_RECIPIENTS into @USER
        END
    deallocate CUR_RECIPIENTS

    --the mail body
    set @FIRST_BEGIN_YYYYMMDD = convert(char(8),dateadd(day,1,getdate()),112)
    set @FIRST_END_YYYYMMDD = convert(char(8),dateadd(day,8,getdate()),112)
    set @SECOND_BEGIN_YYYYMMDD = convert(char(8),dateadd(day,9,getdate()),112)
    set @SECOND_END_YYYYMMDD = convert(char(8),dateadd(day,16,getdate()),112)

    set @varMessage = 'the mail instruction'
    set @varMessage = @varMessage + '　　the time of Number 1 operation(YYYYMMDD)：' +
                    SUBSTRING(@FIRST_BEGIN_YYYYMMDD, 1, 4) +
                    SUBSTRING(@FIRST_BEGIN_YYYYMMDD, 5, 2) +
                    SUBSTRING(@FIRST_BEGIN_YYYYMMDD, 7, 2) + ' TO ' +
                    SUBSTRING(@FIRST_END_YYYYMMDD, 1, 4) +
                    SUBSTRING(@FIRST_END_YYYYMMDD, 5, 2) +
                    SUBSTRING(@FIRST_END_YYYYMMDD, 7, 2) + CHAR(10)
    set @varMessage = @varMessage + '　　the time of Number 1 operation(YYYYMMDD)：' +
                    SUBSTRING(@SECOND_BEGIN_YYYYMMDD, 1, 4) +
                    SUBSTRING(@SECOND_BEGIN_YYYYMMDD, 5, 2) +
                    SUBSTRING(@SECOND_BEGIN_YYYYMMDD, 7, 2) + ' TO ' +
                    SUBSTRING(@SECOND_END_YYYYMMDD, 1, 4) +
                    SUBSTRING(@SECOND_END_YYYYMMDD, 5, 2) +
                    SUBSTRING(@SECOND_END_YYYYMMDD, 7, 2) + CHAR(10)

-- set @varMessage = @varMessage + other contents and my signature

    print @varRecipient
    print @varMessage
    set @varSubject = '【' + @YEAR + @SEASON + 'other subject' + '】'

--就是下面这语句临阵退缩,使收件人成了公司的所有领导,当时吓得一身冷汗啊.

--但是我记得很清楚,测试之前,把这句已加进下面这句的.

--现在还没有弄明白SQL SERVER是怎样保存这些代码的.

--因为之后再次打开这个存储过程时,发现下面这句话不在本存储过程中
--    set @varRecipient = my mail address

    --mail sending
    exec master.dbo.xp_sendmail @recipients=@varRecipient,
                                @subject=@varSubject,
                                @copy_recipients=@varCc_recipient,
                                @blind_copy_recipients=@varBcc_recipient,
                                @message=@varMessage
END