Использование курсора в dbMail в SQL Server 2008
Я пытаюсь отправить информацию о денежном переводе нашим клиентам, указав номер чека и сумму на чеке. Я не могу отправить одно электронное письмо для каждого клиента с номером чека, суммой и информацией о переводе.
В настоящее время электронная почта генерируется, но она выбирает только одного клиента и отправляет электронные письма, равные количеству записей, которые были получены. Пожалуйста, посмотрите на мой запрос ниже, возможно, я ошибаюсь в части курсора.Новый код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=''
Select @body=@body+''+T0.docnum+''+t0.cardcode+''
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+''
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 =''+@cardcode +'Dear Vendor,<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></p>'
set @table =
N'Remittance Info' + N''+
N'DocnumnumcardchecknumCheckAmtCheckDateEMAIL' +
'' + @docnum + '' +
'' + @numatcard + '' +
'' + @checknum + ''+
''+ @checkAmt + ''+
''+@Date + ''+
''+ @EMail +
''+ @Cardcode +''
Set @body=''+@message+@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