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

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

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