|
Hi,
My objective is to send out invoice statements with database mail by means of a stored procedure.
There are two tables, Table1 is customer information, Table2 is invoice information.
Table 1 has Account Number, Customer Email, etc..
Table2 has Account Number, Invoice Number, etc..
This is one of my failed attempts trying to use Cursors.
Any suggestion on how I can accomplish this.
Thank you.
declare @msg varchar(2000),
@subject varchar(30),
@statedate varchar(50),
@accountno varchar(50),
@amountdue nchar(10),
@companyname varchar(50),
@stateaddress varchar(50),
@statecity varchar(50),
@st varchar(50),
@statezip nchar(10),
@comment varchar(50),
@statecurrent varchar(50),
@day3160 int,
@day6190 int,
@over90 int,
@fc money,
@email varchar(50),
@AccountNo1 varchar(50),
@InvoiceDate varchar(50),
@InvoiceNo varchar(50),
@Rt varchar(50),
@CustomerNo varchar(50),
@Charges varchar(50),
@Credits varchar(50),
@Balance varchar(50)
declare email_cur CURSOR
for
select StateDate, AccountNo, AmountDue,
companyName, StateAddress, StateCity,
St, StateZip, Comment,
StateCurrent, day3160, day6190,
over90, FinanceCharge, Email
from Table1
open email_cur
fetch next from email_cur into
@statedate,
@accountno,
@amountdue,
@companyname,
@stateaddress,
@statecity,
@st,
@statezip,
@comment,
@statecurrent,
@day3160,
@day6190,
@over90,
@fc,
@email
while (@@Fetch_status=0)
begin
declare email_cur1 CURSOR
for
select AccountNo, InvoiceDate,
InvoiceNo, Rt, CustomerNo,
Charges, Credits, Balance
from Table2
where accountno = @accountno
open email_cur1
fetch next from email_cur1 into
@AccountNo1,
@InvoiceDate,
@InvoiceNo,
@Rt,
@CustomerNo,
@Charges,
@Credits,
@Balance
while (@@Fetch_status=0)
begin
fetch next from email_cur1 into
@AccountNo1,
@InvoiceDate,
@InvoiceNo,
@Rt,
@CustomerNo,
@Charges,
@Credits,
@Balance
end
deallocate email_cur1
set @msg= ' html stuff e.g.
<tr>
<td width="17%">' + @InvoiceDate + '</td>
<td width="16%">' + @InvoiceNo + '</td>
<td width="7%">' + @Rt + '</td>
<td width="11%">' + @CustomerNo + '</td>
<td width="15%">' + @Charges + '</td>
<td width="17%">' + @Credits + '</td>
</tr>'
exec msdb.dbo.sp_send_dbmail
@profile_name = 'emailer',
@recipients=@email, @body=@msg,
@subject='Invoice Statement',
@body_format = 'HTML'
fetch next from email_cur into
@statedate,
@accountno,
@amountdue,
@companyname,
@stateaddress,
@statecity,
@st,
@statezip,
@comment,
@statecurrent,
@day3160,
@day6190,
@over90,
@fc,
@email
end
deallocate email_cur
|
