sqlserver2000利用存储过程发邮件

yin_jw

业务逻辑
alter proc exec_sendmail1@appname varchar(100), --申请人@guid varchar(100),    --表单guid@id varchar(100),      --表单编号@title varchar(500)    --邮件标题asdeclare @from varchar(500)  --发件箱declare @smtpserver varchar(200) --smtp服务器declare @sendusername varchar(200) --发送认证:用户名declare @sendpassword varchar(200) --发送认证:密码 declare @content varchar(2000) --邮件内容select @from = 'OA_SANYO@sanyo.com'select @smtpserver = '10.25.21.51'select @sendusername = 'OA_SANYO@sanyo.com'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 intset @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  --限制循环次数为countdeclare 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 tempCursordeclare @name varchar(50) --收件人fetch next from tempCursor into @namewhile @@fetch_status=0begin--向临时表中插数据insert into #temp(name) values(@name)fetch next from tempCursor into @nameendclose tempCursordeallocate tempCursor--声明游标发邮件declare sendMailCursor cursorfor  select distinct(name) from #temp   open sendMailCursordeclare @receiptant varchar(50) --收件人declare @to varchar(500) --收件箱fetch next from sendMailCursor into @receiptantwhile @@fetch_status=0begin--调用存储过程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 = @contentfetch next from sendMailCursor into @receiptantendclose sendMailCursordeallocate sendMailCursorgoexec 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