SQL2008 Email

USE [MES]GO/****** Object: StoredProcedure [dbo].[SendMail] Script Date: 04/08/2009 17:44:28 ******/SET ANSI_NULLS OFFGOSET QUOTED_IDENTIFIER OFFGOALTER Procedure [dbo].[SendMail]@Sender varChar(50)=null, --發件人地址@SenderName varChar(50)=null,--發件人姓名@strRecipients varChar(200),--收件人地址，多個人用";"分開@strSubject varChar(200),--主旨@strMessage varChar( 2000),--正文@sql varChar(50)=null --如果有附件，可以在這裡寫SQL語句AsDeclare @SplitStr varchar(1) --定义邮件地址分割符变量Declare @strTemp varchar(200) --定义多个收件人字符串临时变量Declare @email varchar(50) --用分割符分割后的单个收件人字符串变量Declare @SenderAddress varChar(50)Declare @Attach varChar(200)Declare @DefaultSender varChar(50)Declare @MailServer varChar(50)Declare @User varChar(50)Declare @Pass varChar(50)--Declare @SenderName varChar(50)Declare @AttachDir varChar(100)--初始化缺省变量Set @DefaultSender='[e-mail]'Set @MailServer='172.20.100.21'Set @User='jufei'Set @Pass='jiang12475+'--Set @SenderName='執行結果'Set @AttachDir='E:/LOG/WebData/Jmail/'+Replace(Replace(Replace(Convert(varChar(19),GetDate(),120),'-',''),' ',''),':','')+'.txt'--将Email地址分割符统一为分号set @SplitStr=';'Set @strTemp=@strRecipients+@SplitStr+'end'Set @strTemp=Replace(@strTemp,',',';')--判斷發件人if (@SenderName is null) or(len(@SenderName)=0)beginSet @SenderName='執行結果'end--判断是否有sql语句If (@Sql is Null) Or (len(@Sql)=0)Set @AttachDir=NullElseBeginDeclare @CmdStr varChar(200)Set @CmdStr='bcp "'+@Sql+'" queryout '+@AttachDir+' -c'EXEC master..xp_cmdshell @CmdStrEndwhile CharIndex(@SplitStr,@strTemp,1)<>0BeginSet @email=left(@strTemp,CharIndex(@SplitStr,@strTemp,1)-1)Set @strTemp=right(@strTemp,len(@strTemp)-len(@email)-1)If (@Sender Is Null) Or (Len(@Sender)=0)Set @SenderAddress=@DefaultSenderElseSet @SenderAddress=@Sender--Print @emailEXEC sp_jmail_send @sender=@SenderAddress,@sendername=@SenderName,@serveraddress=@MailServer,@MailServerUserName=@User,@MailServerPassword=@Pass,@recipient=@email,@subject=@strSubject,@mailbody=@strMessage,@attachment=@AttachDirend