在sql server 2008中的dbMail中使用游标 [英] Using cursor in dbMail in sql server 2008

查看：186
本文介绍了在sql server 2008中的dbMail中使用游标的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我想透过支票上的支票号码和金额向我们的客户发送汇款信息。我无法为每个客户发送一封电子邮件，其中包含支票号码和金额以及汇款信息。



现在电子邮件已生成，但它只挑选一个客户并发送等于被拉取的记录数的电子邮件。



新代码



<$ h p $ p> Declare @body nvarchar（max）
declare @docnum nvarchar（50）
declare @numatcard nvarchar（50）
declare @checknum nvarchar
declare @checkAmt nvarchar（100）
declare @EMaIL nvarchar（200）
declare @Date nvarchar（200）
declare @table nvarchar（max）
declare @ message nvarchar（Max）
declare @cardcode nvarchar（200）


为
声明EmailCursor游标选择t5.checknum，T5.CheckSum，T3.E_Mail

from SAP_PROD。[dbo]。[OVPM] T0 INNER JOIN SAP_PROD。[dbo]。[VPM2] T1 ON T0.DocNum = T1.DocNum
left join SAP_PROD。[dbo] .VPM1 T5 on T0.DocEntry = T5.docnum
left JOIN SAP_PROD。[dbo] .OPCH T2 ON T1.DocEntry = T2.DocEntry And T1.InvType = T2.ObjType
left JOIN SAP_PROD。[dbo] .ORPC T4 ON T1.DocEntry = T4.DocEntry And T1.InvType = T4.ObjType
left JOIN SAP_PROD。[dbo] .ORIN T6 ON T1.DocEntry = T6.DocEntry And T1.InvType = T6.ObjType
left join SAP_PROD。[dbo] .OCRD T3 on T3.CardCode = T0.CardCode
其中T0。[DocDate] = CAST（getdate（） - 13作为日期）

打开EmailCursor

从EmailCursor下一步获取到@checknum，@checkAmt，@Email

while（@@ FETCH_STATUS = 0）
Begin

set @body ='< table>'
选择@ body = @ body +'< tr>< td>'+ T0.docnum +'< / td>'+ t0.cardcode + / d>< / tr>< / tr>'

来自SAP_PROD。[dbo]。[OVPM] T0 INNER JOIN SAP_PROD。[dbo]。[VPM2] T1 ON T0.DocNum = T1.DocNum
left join SAP_PROD。[dbo] .VPM1 T5 on T0.DocEntry = T5.docnum
left JOIN SAP_PROD。[dbo] .OPCH T2 ON T1.DocEntry = T2.DocEntry和T1.InvType = T2.ObjType
left JOIN SAP_PROD。[dbo] .ORPC T4 ON T1.DocEntry = T4.DocEntry And T1.InvType = T4.ObjType
left JOIN SAP_PROD。[dbo] .ORIN T6 ON T1.DocEntry = T6 .DocEntry And T1.InvType = T6.ObjType
left join SAP_PROD。[dbo] .OCRD T3 on T3.CardCode = T0.CardCode
其中
T5.checknum=@checknum

Set @ body = @ body +'< / table>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name ='abc'，
@ recipients = @ email，
@subject ='汇款'，
@body = @body，
@body_format ='HTML'

FETCH NEXT FROM EmailCursor INTO @ checknum，@ checkAmt，@ EMAIL
END
关闭EmailCursor
Deallocate EmailCursor





*旧代码*** 

  ------------------------------------------------ ------------------------------------- 
 
 
申报@body nvarchar（max）
为
声明EmailCursor游标从
 SAP_PROD。[dbo]中选择不同的t5.checknum，T5.CheckSum，T3.E_Mail 
 
。 [OVPM] T0 INNER JOIN 
 SAP_PROD。[dbo]。[VPM2] T1 ON T0.DocNum = T1.DocNum 
 left join 
 SAP_PROD。[dbo] .VPM1 T5 on T0.DocEntry = T5.docnum 
 --left JOIN 
 SAP_PROD。[dbo] .OPCH T2 ON T1.DocEntry = T2.DocEntry And T1.InvType = T2.ObjType 
 --left JOIN 
 SAP_PROD。[dbo] .ORPC T4 ON T1.DocEntry = T4.DocEntry和T1.InvType = T4.ObjType 
 --left JOIN 
 SAP_PROD。[dbo] .ORIN T6 ON T1.DocEntry = T6.DocEntry And T1.InvType = T6.ObjType 
 left join 
 SAP_PROD。[dbo] .OCRD T3 on T3.CardCode = T0.CardCode 
其中T0。[DocDate] = CAST （getdate（） -  9 as date）
 
打开EmailCursor 
 
声明@docnum nvarchar（50）
声明@numatcard nvarchar（50）
 @checknum nvarchar（50）
 declare @checkAmt nvarchar（100）
 declare @EMaIL nvarchar（200）
 declare @Date nvarchar（200）
 declare @table nvarchar 
 declare @message nvarchar（Max）
 declare @cardcode nvarchar（50）
 
接下来从EmailCursor获取到@ Checknum，@ checkamt，@ email 
 
 While（@@ FETCH_STATUS = 0）
 Begin 
 
 
 
 select 
 @docnum = t0.docnum，
 
 @numatcard = t2.numatcard，
 @checknum = t5.checknum，
 @ Checkamt = T5.CheckSum，
 @ EMAIL = T3.E_Mail，
 @ Date = Convert（Nvarchar （12），T0.Docdate，101），
 @ Cardcode = T0。[CardName] 
 
 
 from 
 SAP_PROD。[dbo]。 INNER JOIN SAP_PROD。[dbo]。[VPM2] T1 ON T0.DocNum = T1.DocNum 
 left join SAP_PROD。[dbo] .VPM1 T5 on T0.DocEntry = T5.docnum 
 inner JOIN 
 SAP_PROD。[dbo] .OPCH T2 ON T1.DocEntry = T2.DocEntry And T1.InvType = T2.ObjType 
 left JOIN 
 SAP_PROD。[dbo] .ORPC T4 ON T1.DocEntry = T4 .DocEntry And T1.InvType = T4.ObjType 
 left JOIN 
 SAP_PROD。[dbo] .ORIN T6 ON T1.DocEntry = T6.DocEntry和T1.InvType = T6.ObjType 
 left join 
 SAP_PROD。[dbo] .OCRD T3 on T3.CardCode = T0.CardCode 
其中T5.checknum=@checknum 
 set @message ='< th>< td>'+ @cardcode +'尊敬的供应商，< / th>< / td>< / br> 
检查编号：'+ @checknum +'的数额'+ @ checkAmt +'已由Teq处理，将在48小时内邮寄。请参阅下面的汇款信息。< / br> 
 
如果您对此付款有任何问题或疑虑，请联系我们应付帐款工作人员的成员。< / br>< / br> 
 
谨慎，< / br> 
< / br> 
应付帐款部门< / br> 
 
< / br> 
 
< / br> 
 
< / p>'
 
 
 set @table = 
 N'< H1 title =Talign = center>汇款信息; / H1>'+ N' N'< table>< tr& / th>第< / th>< / tr>< / th>< / th>< / th> 

'< tr>< td>'+ @docnum +'< / td>'+ 
'< td>'+ @numatcard +'< / td> '+ 
'< td>'+ @checknum +'< / td>'+ 
'< td>'+ @checkAmt +'< / td>'+ 
 '< td>'+ @ Date +'< / td>'+ 
'< td>'+ @EMail + 
'< td>'+ @Cardcode +'< td>< / tr>< / table>'



 Set @body ='< table>< tr>< th> @table +'< / td>< / tr>< / table>'

 EXEC msdb.dbo.sp_send_dbmail 
 @profile_name ='abc'，
 @recipients = @ email，
 @subject ='汇款'，
 @body = @body，
 @body_format ='HTML'
 FETCH NEXT FROM EmailCursor INTO @ checknum，@ checkAmt， @EMAIL 
 END 
关闭EmailCursor 
 Deallocate EmailCursor 
 
 
方案
我想你想在游标内建立你的电子邮件 -  
  DECLARE @body nvarchar（max）
 DECLARE EmailCursor CURSOR FOR 
 SELECT checknum，checkamt，email FROM ....  - 每个电子邮件一行
 
 OPEN EmailCursor 
 FETCH NEXT FROM EmailCursor INTO @checknum，@checkAmt，@EMAIL 
 WHILE（@@ FETCH_STATUS = 0）
 BEGIN 
  - 在此处构建电子邮件
 set @body ='< table>'
 select @body = @body +'< tr>< td>'+ docnum +'< / td>'
 
  - ....此处的其他字段
 +'< td>'+ Cardcode +'< / td>< / tr>'
从 -  .... 
 checknum = @checknum  - 或者什么给出这个上下文
 set @body = @body +'< / table>'
 
 exec msdb.dbo.sp_send_dbmail  -  ... 
 
 FETCH NEXT FROM EmailCursor INTO @checknum，@checkAmt，@EMAIL 
 end 
  
 
I am trying to send remittance info to our customers with check# and amount on the check. I am unable to send one email per customer with check num and amount and remittance info. 
Right now  email gets generated but it is only picking one customer and sending emails equal to num of records which get pulled. Please have a look at my query below, probably I am messing up in the cursor part.
New code
Declare @body nvarchar(max)
declare @docnum   nvarchar(50) 
declare @numatcard  nvarchar(50) 
declare @checknum nvarchar(50) 
declare @checkAmt nvarchar(100)
declare @EMaIL     nvarchar(200)
declare @Date      nvarchar(200)
declare @table    nvarchar(max)
declare @message   nvarchar(Max)
declare @cardcode nvarchar(200)


Declare EmailCursor Cursor for
Select t5.checknum,T5.CheckSum,T3.E_Mail

from SAP_PROD.[dbo].[OVPM]T0 INNER JOIN SAP_PROD.[dbo].[VPM2]  T1 ON T0.DocNum = T1.DocNum 
left join SAP_PROD.[dbo].VPM1 T5 on T0.DocEntry=T5.docnum
left JOIN SAP_PROD.[dbo].OPCH T2 ON T1.DocEntry = T2.DocEntry And T1.InvType=T2.ObjType
left JOIN SAP_PROD.[dbo].ORPC T4 ON T1.DocEntry = T4.DocEntry And T1.InvType=T4.ObjType
left JOIN SAP_PROD.[dbo].ORIN T6 ON T1.DocEntry = T6.DocEntry And T1.InvType=T6.ObjType
left join SAP_PROD.[dbo].OCRD T3 on T3.CardCode=T0.CardCode
where T0.[DocDate]=CAST(getdate()-13 as date)

Open EmailCursor

Fetch next from EmailCursor into @checknum, @checkAmt, @Email

While (@@FETCH_STATUS=0)
Begin

set @body='<table>'
Select @body=@body+'<tr><td>'+T0.docnum+'</td>'+t0.cardcode+'</td></tr>'

from SAP_PROD.[dbo].[OVPM]T0 INNER JOIN SAP_PROD.[dbo].[VPM2]  T1 ON T0.DocNum = T1.DocNum 
left join SAP_PROD.[dbo].VPM1 T5 on T0.DocEntry=T5.docnum
left JOIN SAP_PROD.[dbo].OPCH T2 ON T1.DocEntry = T2.DocEntry And T1.InvType=T2.ObjType
left JOIN SAP_PROD.[dbo].ORPC T4 ON T1.DocEntry = T4.DocEntry And T1.InvType=T4.ObjType
left JOIN SAP_PROD.[dbo].ORIN T6 ON T1.DocEntry = T6.DocEntry And T1.InvType=T6.ObjType
left join SAP_PROD.[dbo].OCRD T3 on T3.CardCode=T0.CardCode
where
T5.checknum=@checknum

Set @body=@body+'</table>'
   EXEC msdb.dbo.sp_send_dbmail 
      @profile_name = 'abc',
      @recipients=@email,
    @subject = 'Remittance',
    @body = @body,
    @body_format = 'HTML'

FETCH NEXT FROM EmailCursor INTO  @checknum,@checkAmt,@EMAIL
END
Close EmailCursor
Deallocate EmailCursor  




*Old code***
-------------------------------------------------------------------------------------


 Declare @body nvarchar(max)
    Declare EmailCursor Cursor for
    Select   distinct t5.checknum,T5.CheckSum,T3.E_Mail

    from 
SAP_PROD.[dbo].[OVPM]T0 INNER JOIN 
SAP_PROD.[dbo].[VPM2]  T1 ON T0.DocNum = T1.DocNum 
    left join 
SAP_PROD.[dbo].VPM1 T5 on T0.DocEntry=T5.docnum
    --left JOIN 
SAP_PROD.[dbo].OPCH T2 ON T1.DocEntry = T2.DocEntry And T1.InvType=T2.ObjType
    --left JOIN 
SAP_PROD.[dbo].ORPC T4 ON T1.DocEntry = T4.DocEntry And T1.InvType=T4.ObjType
    --left JOIN 
SAP_PROD.[dbo].ORIN T6 ON T1.DocEntry = T6.DocEntry And T1.InvType=T6.ObjType
    left join 
SAP_PROD.[dbo].OCRD T3 on T3.CardCode=T0.CardCode
    where T0.[DocDate]=CAST(getdate()-9 as date)

    Open EmailCursor

    declare @docnum   nvarchar(50) 
    declare @numatcard  nvarchar(50) 
    declare @checknum nvarchar(50) 
    declare @checkAmt nvarchar(100)
    declare @EMaIL     nvarchar(200)
    declare @Date      nvarchar(200)
    declare @table    nvarchar(max)
    declare @message   nvarchar(Max)
    declare @cardcode nvarchar(50)

    Fetch next from EmailCursor into @Checknum,@checkamt,@email

    While(@@FETCH_STATUS=0)
    Begin



    select 
     @docnum   = t0.docnum,

     @numatcard  = t2.numatcard,
    @checknum = t5.checknum,
    @Checkamt= T5.CheckSum,
    @EMAIL= T3.E_Mail ,
    @Date=Convert(Nvarchar(12),T0.Docdate,101) ,
    @Cardcode=T0.[CardName]   


    from
   SAP_PROD.[dbo].[OVPM]T0 INNER JOIN SAP_PROD.[dbo].[VPM2]  T1 ON T0.DocNum = T1.DocNum 
    left join SAP_PROD.[dbo].VPM1 T5 on T0.DocEntry=T5.docnum
    inner JOIN 
SAP_PROD.[dbo].OPCH T2 ON T1.DocEntry = T2.DocEntry And T1.InvType=T2.ObjType
    left JOIN 
SAP_PROD.[dbo].ORPC T4 ON T1.DocEntry = T4.DocEntry And T1.InvType=T4.ObjType
    left JOIN 
SAP_PROD.[dbo].ORIN T6 ON T1.DocEntry = T6.DocEntry And T1.InvType=T6.ObjType
    left join 
SAP_PROD.[dbo].OCRD T3 on T3.CardCode=T0.CardCode
     where T5.checknum=@checknum
    set @message ='<th><td>'+@cardcode  +'Dear Vendor,</th></td></br> 
    Check num : ' + @checknum + ' in the amount of ' + @checkAmt+  ' has been processed by Teq and will be mailed within 48 hours. Please see remittance information below.</br> 

    If you have any questions or concerns regarding this payment please contact a member of our Accounts Payable staff.</br></br>

    Regards,</br>
    </br>
    Accounts Payable Department</br>

</br>

</br>

 </p>'


    set @table   =
      N'<H1 title="T" align=center>Remittance Info</H1>' + N'<table border="2" bordercolor=#F5FFFA bgcolor=#566D7E align=center>'+
                   N'<table><tr><th>Docnum</th><th>numcard</th><th>checknum<th>CheckAmt</th><th>CheckDate</th><th>EMAIL</th></tr>' +

                   '<tr><td>' + @docnum + '</td>' +
                    '<td>' + @numatcard + '</td>' +
                    '<td>' + @checknum + '</td>'+
                    '<td>'+ @checkAmt + '</td>'+
                    '<td>'+@Date + '</td>'+
                    '<td>'+ @EMail + 
                     '<td>'+ @Cardcode +'</td></tr></table>' 



       Set @body='<table><tr><th>'+@message+@table+'</td></tr></table>'   

       EXEC msdb.dbo.sp_send_dbmail 
          @profile_name = 'abc',
          @recipients=@email,
        @subject = 'Remittance',
        @body = @body,
        @body_format = 'HTML'
    FETCH NEXT FROM EmailCursor INTO  @checknum,@checkAmt,@EMAIL
    END
    Close EmailCursor
    Deallocate EmailCursor          



 解决方案 
I think you would want to build your email inside the cursor - something along the lines of 
DECLARE @body nvarchar(max)
DECLARE EmailCursor CURSOR FOR 
  SELECT checknum, checkamt, email FROM .... -- one row per required email

OPEN EmailCursor
FETCH NEXT FROM EmailCursor INTO  @checknum, @checkAmt, @EMAIL
WHILE (@@FETCH_STATUS = 0)
BEGIN
  -- do the bit to build email in here 
  set @body = '<table>'
  select @body = @body + '<tr><td>' + docnum + '</td>'

-- .... rest of fields here
                       + '<td>'+ Cardcode +'</td></tr>'
  from -- .... 
  where checknum = @checknum -- or whatever gives this context
  set @body = @body + '</table>'

  exec  msdb.dbo.sp_send_dbmail -- ...

  FETCH NEXT FROM EmailCursor INTO  @checknum, @checkAmt, @EMAIL
end



                        
这篇关于在sql server 2008中的dbMail中使用游标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

                        查看全文
                    

                            
                        

            



        

            
相关文章

            
        

            



        

        

            
HTML/CSS最新文章

            
        

        

            

                热门教程
            

            
        

        

            

                热门工具
            

            
        

        
    

    

        

            登录
            关闭
        

        

            

                扫码关注1秒登录
            

            

                
            

            

                
                
            

            

                发送“验证码”获取
                |
                15天全站免登陆
            

            

        

    



    

		

			友情链接：
            IT屋
            Chrome插件
            谷歌浏览器插件
        

        

            IT屋
            ©2016-2022 琼ICP备2021000895号-1
            站点地图
            站点标签
            SiteMap
            <免责申明>
            本站内容来源互联网,如果侵犯您的权益请联系我们删除.