sqlserver2000利用存储过程发邮件

业务逻辑

alter proc exec_sendmail1
@appname varchar(100), --申请人
@guid varchar(100),    --表单guid
@id varchar(100),      --表单编号
@title varchar(500)    --邮件标题
as

declare @from varchar(500)  --发件箱
declare @smtpserver varchar(200) --smtp服务器
declare @sendusername varchar(200) --发送认证:用户名
declare @sendpassword varchar(200) --发送认证:密码 
declare @content varchar(2000) --邮件内容

select @from = '[e-mail]'
select @smtpserver = '10.25.21.51'
select @sendusername = '[e-mail]'
select @sendpassword = '20369845'
select @content = @appname+'提交的'+@title+'('+@id+')'+'，审核不通过，已回退，请知晓！'

--判断是否为空
if @appname = '' or  @guid = '' or @id = '' or @title = ''
begin  
  raiserror 50000 'please set the @appname and  @guid and @id and @title values before excute the stored procedure'  
  return -1  
end

--动态设定查询行数
declare @count int
set @count = ((select count(*) from sys_inst_prcs 
              where taskid = @guid 
              and prc_id >= 
              (
                select top 1 prc_id from sys_inst_prcs 
                where selactname = 0 
                and taskid = @guid 
                and procuser is not null 
                order by prc_id desc
              ))-1)
print @count
--创建临时表
create table #temp(name varchar(50))

--申明游标往临时表中插值
set rowcount @count  --限制循环次数为count
declare tempCursor cursor 
for
  select procuser from sys_inst_prcs 
  where taskid = @guid 
    and prc_id >= 
    (
      select top 1 prc_id from sys_inst_prcs 
      where selactname = 0 
      and taskid = @guid  
      and procuser is not null 
      order by prc_id desc
    ) 

open tempCursor
declare @name varchar(50) --收件人
fetch next from tempCursor into @name
while @@fetch_status=0
begin

--向临时表中插数据
insert into #temp(name) values(@name)
fetch next from tempCursor into @name
end
close tempCursor
deallocate tempCursor

--声明游标发邮件
declare sendMailCursor cursor
for
  select distinct(name) from #temp 
  
open sendMailCursor
declare @receiptant varchar(50) --收件人
declare @to varchar(500) --收件箱

fetch next from sendMailCursor into @receiptant
while @@fetch_status=0
begin
--调用存储过程send_mail发邮件
set @to = (select email from sys_user where username = @receiptant)
exec send_mail @from=@from, @to = @to,@smtpserver = @smtpserver,@sendusername = @sendusername,@sendpassword = @sendpassword,@subject = @title,@body = @content
fetch next from sendMailCursor into @receiptant
end
close sendMailCursor
deallocate sendMailCursor

go


exec exec_sendmail1 '陆宁','dad42bed-73c3-48aa-ab06-7962b335f0ff','NJSQ11060805383','年假申请表单'

执行过程：当审批不同意时，查询出前面审批过的人的邮箱，利用游标循环查询结果，调用存储过程发送邮件给已经审批过的人，通知申请表单已经回退

参考链接：

http://chenxing.blog.51cto.com/240526/44621

执行发邮件

ALTER PROCEDURE [dbo].[send_mail]  
   @From varchar(1000) ='',  --发件人  
   @To varchar(1000) ,   --收件人  
   @smtpserver varchar(200),--smtp服务器
   @sendusername varchar(200),--发送认证:用户名
   @sendpassword varchar(200),--发送认证:密码 
   @Subject nvarchar(128)='', --标题  
   @Body nvarchar(4000) ='' --正文  
   with encryption  
      
   /*********************************************************************  
      
   This stored procedure takes the parameters and sends an e-mail.  
   All the mail configurations are hard-coded in the stored procedure.  
   Comments are added to the stored procedure where necessary.  
   References to the CDOSYS objects are at the following MSDN Web site:  
   http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp     
   ***********************************************************************/  
  AS  
  Declare @iMsg int  
  Declare @hr int  
  Declare @source varchar(255)  
  Declare @description varchar(500)  
  Declare @output varchar(1000)     
    
  if @sendusername='' or  @sendpassword=''  
  begin  
   raiserror 50000 'please set the @sendusername and  @sendpassword values before excute the stored procedure'  
   return -1  
  end     
  --replace the quotation marks  
  set @Subject=replace(@Subject,'''','''''')  
  set @Body=replace(@Body,'''','''''')     
  --************* Create the CDO.Message Object ************************  
  EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT     
  --***************Configuring the Message Object ******************  
  -- This is to configure a remote SMTP server.  
  -- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp  
  EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'  
  -- This is to configure the Server Name or IP address.  
  -- Replace MailServerName by the name or IP of your SMTP Server.  
  EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', @smtpserver  
  --这个需要注意：如果不将其值设为0，会报错，不知道为什么  
  EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").Value','0'     
  EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusername").Value', @sendusername  
  EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").Value', @sendpassword  
     
  -- Save the configurations to the message object.  
  EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null  
     
  -- Set the e-mail parameters.  
  EXEC @hr = sp_OASetProperty @iMsg, 'To', @To  
  EXEC @hr = sp_OASetProperty @iMsg, 'From', @From  
  EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject  
     
  -- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.  
  EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBody', @Body  
  --选择了HTMLBody格式，就必须设置HTMLBodyPart，否则会出现乱码
  EXEC @hr = sp_OASetProperty @IMsg, 'HTMLBodyPart.Charset', 'gb2312'
  EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL  
     
  if @@error<>0 or @hr<>0  
  begin  
   raiserror 55000 '<send_mail> Error: send mail failed.'  
  end  
  else   
  begin  
   print 'Success: send mail ok.'  
  end  
     
  EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT  
  IF @hr = 0  
  BEGIN  
   SELECT @output = '<send_mail> Error Source: ' + @source  
   PRINT  @output  
   SELECT @output = '<send_mail> Error Description: ' + @description  
   PRINT  @output  
  END  
  ELSE  
  BEGIN  
   PRINT '  sp_OAGetErrorInfo failed.'  
   RETURN  
  END  
    
  -- Do some error handling after each step if you have to.  
  -- Clean up the objects created.  
  EXEC @hr = sp_OADestroy @iMsg  
   
  GO  

 

参考链接：

http://topic.csdn.net/u/20100322/15/59f780a9-1a7a-4974-a830-7d80d3e90648.html

http://www.cnblogs.com/NeoLee/archive/2005/03/28/127316.html