Using cursor in dbMail in sql server 2008

Question

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

if the select statement within the cursor can returns more than one row then you will need to use the method I demonstrated in my answer to build your html table. The way you are doing it the variables `@docnum` etc can only be set to the value in one row. In the answer I posted it is using the actual fields from the select to build the html string — Ian Kenney, Jun 27 '13 at 18:15

score 1 · Accepted Answer · answered Jun 27 '13 at 15:37

1

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

answered Jun 27 '13 at 15:37

Ian Kenney

6,376
1
25
44

Ian, your suggestion solved one problem, now it is sending emails to customers according to their check num and amounts but in the table for remittance, it is only picking the top row. for remittance I want to show all the invoices which were used for cutting a particular check. I have pasted new code according to your suggestion. thankyou soo much. – user2363530 Jun 27 '13 at 17:54
Ian, I have pasted the code above according to your suggestion. – user2363530 Jun 27 '13 at 18:37
I saw, see my comment under the question – Ian Kenney Jun 27 '13 at 19:02
I have added new code, but now I am getting the this error: Msg 245, Level 16, State 1, Line 32 Conversion failed when converting the nvarchar value '
' to data type int
– user2363530 Jul 01 '13 at 13:16

Using cursor in dbMail in sql server 2008

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

1 Answers1

Linked

Related