USE [MES] GO /****** Object: StoredProcedure [dbo].[SendMail] Script Date: 04/08/2009 17:44:28 ******/ SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO ALTER 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語句 As Declare @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='SqlServer@geniusnet.com.tw' 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) begin Set @SenderName='執行結果' end --判断是否有sql语句 If (@Sql is Null) Or (len(@Sql)=0) Set @AttachDir=Null Else Begin Declare @CmdStr varChar(200) Set @CmdStr='bcp "'+@Sql+'" queryout '+@AttachDir+' -c' EXEC master..xp_cmdshell @CmdStr End while CharIndex(@SplitStr,@strTemp,1)<>0 Begin Set @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=@DefaultSender Else Set @SenderAddress=@Sender --Print @email EXEC sp_jmail_send @sender=@SenderAddress,@sendername=@SenderName, @serveraddress=@MailServer,@MailServerUserName=@User,@MailServerPassword=@Pass, @recipient=@email,@subject=@strSubject,@mailbody=@strMessage,@attachment=@AttachDir end