--下面的代码根据目前项目中的所用到的存储过程改写而成.
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