在sql server 2008中使用dbMail中的游标

Question

我正在尝试将汇款信息发送给我们的客户，支票上有支票号和金额。我无法为每位客户发送一封包含支票号码、金额和汇款信息的电子邮件。

现在会生成电子邮件，但它只选择一个客户并发送与被提取的记录数量相等的电子邮件。请看一下我下面的查询，可能我在光标部分搞砸了

新代码

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  

---

*旧代码***

-------------------------------------------------------------------------------------

 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          

Answer 1

我想你应该在光标内构建你的电子邮件——类似的东西

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