470,596 Members | 1,314 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,596 developers. It's quick & easy.

Sending formated Email with database mail sql 2005

1
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
Jun 29 '07 #1
0 1499

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by praba kar | last post: by
5 posts views Thread by Brian Hanson | last post: by
6 posts views Thread by Eduardo Rosa | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.