creating a html email using a sql stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

creating a html email using a sql stored procedure

Author

Topic

genmed
Starting Member

4 Posts

Posted - 2004-11-23 : 11:53:16

Hi
I have a number of SQL stored procedures that interogate my SQL tables using variables and have managed to generate a html email that I can send using cdonts from the sql server.

the problem I have is that I have a single @ variable picking a reference number out of the data table but I have more than 1 result.

I can produce the result in asp and in query analyzer but I cannot replicate the result in my html email.

here is an example of a section of the stored procedure

'<td><FONT size=2>'+@varfirn+' '+@varsurn+'</td>'+
'<td><FONT size=2>'+@vardobn+' </td>'+
'<td><FONT size=2>£'+STR(@varquotm,7,2)+' </td>'+

where it produces a firstname, surname, date of birth and quote amount. I wish to show more 1 name but it repeats the last row.

I apologise for the lack of specific infomation as I am quite new to sql programming.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-11-23 : 13:58:57

sounds like you should be doing something along the lines of:


Declare @pageHTML varchar(4000)

Select @pageHTML = '<body><table>'

Select 
	@pageHTML = @pageHTML+'<tr>'+
	'<td><FONT size=2>'+@varfirn+' '+@varsurn+'</td>'+
	'<td><FONT size=2>'+@vardobn+' </td>'+
	'<td><FONT size=2>£'+STR(@varquotm,7,2)+' </td>'+
	'</tr>'
From <yourTableName>

Select @pageHTML = @pageHTML + '</table></table>'

Select pageHTML = @pageHTML

Corey

genmed
Starting Member

4 Posts

Posted - 2004-11-24 : 05:16:56

Thank you.

Just so I can learn more about this could you point me in the right direction of a website / book that can assist me in using the correct syntax

I will let you know the outcome

Richard

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-24 : 06:47:06

correct syntax for what?
what corey gave you is simple string adding.

if you want to learn about sql syntax this site is great.
check out the books section
http://www.sqlteam.com/store.asp
http://www.sqlteam.com/filtertopics.asp?TopicID=168

Go with the flow & have fun! Else fight the flow

genmed
Starting Member

4 Posts

Posted - 2004-11-24 : 08:42:51

as requested here is a segment of the code in the stored procedure.that generates the email using database data

SELECT @subject = 'WEB: '+@vartyp+' Private Healthcare Quotation ('+@varref+')'
SELECT @body = '<TD align=right><img src=http://www.generalandmedical.com/images/form-logo.gif width=91 height=88></b></font></div></TD>'+
'<Font face=arial size=3><div align=center><b>Private Healthcare Quotation</b><br>'+
'</TR>'+
'</TABLE>'+
'<TABLE width=750 border=0 cellspacing=2 cellpadding=0 align=center>'+
'<TR>'+
'<TD width=450><div align=left><FONT size=2><B>Quotation Reference:'+@varref+'</B></div></TD>'+
'<TD width=300 align=left><FONT size=2><b>Quotation date:</b>'+@vardat+' </div></TD>'+
'</TR>'+
'<TR>'+
'<TD> </TD>'+
'<TD align=left><FONT size=1>This quotation is valid until '+@varqexp+'</TD>'+
'</TR>'+
'</TABLE>'+
'<br>'+
'<TABLE width=750 border=0 align=center cellpadding=0 cellspacing=2>'+
' <TR>'+
' <TD width=450><div align=left><FONT size=2><B>Name:</B></div></TD>'+
' <TD width=300><div align="left"><FONT size=2>'+@varfir+' '+@varsur+'</div></TD>'+
' </TR>'+
' <TR>'+
' <TD class=tbback_light><div align=left><FONT size=2><B>Occupation:</div></TD>'+
' <TD><FONT size=2>'+@varocc+'</TD>'+
' </TR>'+
' <TR>'+
' <TD><FONT size=2><b>Date of Birth:</b> </TD>'+
' <TD><FONT size=2>'+@vardob+'</TD>'+
' </TR>'+
' <TR>'+
' <TD><FONT size=2><b><div align=left>Address:</b></div></TD>'+
' <TD><div align=left><FONT size=2>'+@varadd+'</div></TD>'+
' </TR>'+
' <TR>'+
' <TD><div align=left><b> </b></div></TD>'+
' <TD><div align=left><FONT size=2>'+@varadda+'</div></TD>'+
' </TR>'+
' <TR>'+
' <TD><div align="left"><FONT size=2><b> </b></div></TD>'+
' <TD><div align="left"><FONT size=2>'+@varaddb+'</div></TD>'+
' </TR>'+
' <TR>'+
' <TD><div align="left"><FONT size=2><b> </b></div></TD>'+
' <TD><div align="left"><FONT size=2>'+@varaddc+'</div></TD>'+
' </TR>'+
' <TR>'+
' <TD><div align="left"><FONT size=2><b>Postcode:</b></div></TD>'+
' <TD><div align="left"><FONT size=2>'+@varpos+'</div></TD>'+
' </TR>'+
' <TR>'+
' <TD><div align="left"><FONT size=2><b>Telephone:</b></div></TD>'+
' <TD><div align="left"><FONT size=2>'+@vartele+'</div></TD>'+
' </TR>'+
' <TR>'+
' <TD><div align="left"><FONT size=2><b>Email Address: </b></div></TD>'+
' <TD><div align="left"><FONT size=2><a href="mailto:'+@varema+'">'+@varema+'</a></div></TD>'+
' </TR>'+
' <TR>'+
' <TD><FONT size=2><b><strong>Where did you hear about us: </b></TD>'+
' <TD><FONT size=2>'+@varsource+'</TD>'+
' </TR>'+
' <TR>'+
' <TD><FONT size=2><b>Previous Insurer:</b></TD>'+
' <TD><FONT size=2>'+@varins+'</TD>'+
' </TR>'+
' <TR>'+
' <TD><FONT size=2><b>Expiry Date: </b></TD>'+
' <TD><FONT size=2>'+@variexp+'</TD>'+
' </TR>'+
' <TR>'+
' </TR>'+
' <TR>'+
' <TD><div align="left"><FONT size=2><b>G & M Cover Name: <FONT size=1>(see Summary of benefits) </b></div></TD>'+
' <TD><div align="left"><FONT size=2>'+@vartyp+'</div></TD>'+
' </TR>'+
' <TR>'+
' <TD><div align="left"><FONT size=2><b>Hospital Network </b></div></TD>'+
' <TD><div align="left"><FONT size=2>'+@varcov+'</div></TD>'+
' </TR>'+
' <TR>'+
'<TD><div align="left"><FONT size=2><b>Underwriting Terms: <FONT size=1>(see Terms and Conditions for definitions)</b></div></TD>'+
'<TD><div align="left"><FONT size=2>'+@varund+'</div></TD>'+
'</TR>'+
'<TR>'+
' <TD><div align="left"><FONT size=2><b>Excess: </b></div></TD>'+
' <TD><div align="left"><FONT size=2>£'+STR(@varexc)+'</div></TD>'+
' </TR>'+
' <TR>'+
' <TD><div align="left"><FONT size=2><b>Main Member Quotation: </b></div></TD>'+
'<TD><div align="left"><FONT size=2>£'+STR(@varquote,7,2)+'</div></TD>'+
'</TR>'+
' <TR>'+
' <TD><div align="left"><FONT size=2><b>Total Monthly Quotation: </b></div></TD>'+
'<TD><div align="left"><FONT size=2>£'+STR(@varval,7,2)+'</div></TD>'+
'</TR>'+
'<TR>'+
'<TD colspan="2"></TD>'+
' <TR>'+
' <TD colspan="2"><FONT size=2><b>Additional Family Members</b>'+

'<table width="100%" border="0" cellspacing="2" cellpadding="0">'+
' <tr>'+
'<td><FONT size=2><b>Name</b></td>'+
'<td><FONT size=2><b>Date of Birth </b></td>'+
'<td><FONT size=2><b>Quotation</b></td>'+
--'<td><FONT size=2><b>id</b></td>'+
'<tr>'+
'<td><FONT size=2>'+@varfirn+' '+@varsurn+'</td>'+
'<td><FONT size=2>'+@vardobn+' </td>'+
'<td><FONT size=2>£'+STR(@varquotm,7,2)+' </td>'+
--'<td><FONT size=2>'+@varid+' </td>'+
'<tr>'+
'<tr>'+
'<td><FONT size=2>'+@varfirn+' '+@varsurn+'</td>'+
'<td><FONT size=2>'+@vardobn+' </td>'+
'<td><FONT size=2>£'+STR(@varquotm,7,2)+' </td>'+
--'<td><FONT size=2>'+@varid+' </td>'+
'<tr>'+
'</table>'

EXEC SP_WEBMAILQ @varema, @subject, @body, 'richard.clare@generalandmedical.com',null,null,'d:\tc.pdf'

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-24 : 09:12:10

advice -- use style sheets so you don't have to keep specifying font sizes and alignments over and over.

- Jeff

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2004-11-26 : 04:21:11

Hi genmed , which database has SP_WEBMAILQ?

Madhivanan

genmed
Starting Member

4 Posts

Posted - 2004-12-06 : 05:51:22

The sp sp_webmailq is detailed below

CREATE PROCEDURE [dbo].[sp_webmailq]
@From varchar(100),
@Subject varchar(100),
@Body varchar(4000),
@To varchar(100),
@CC varchar(100) = null,
@BCC varchar(100) = null,
@Attachment varchar(150) = null,
@Attachment1 varchar(150) = null

AS
Declare @MailID int
Declare @hr int
print @attachment
EXEC @hr = sp_OACreate 'CDONTS.NewMail', @MailID OUT
EXEC @hr = sp_OASetProperty @MailID, 'From',@From
EXEC @hr = sp_OASetProperty @MailID, 'Subject', @Subject
EXEC @hr = sp_OASetProperty @MailID, 'Body', @Body
EXEC @hr = sp_OASetProperty @MailID, 'To', @To
EXEC @hr = sp_OASetProperty @MailID, 'CC', NULL
EXEC @hr = sp_OASetProperty @MailID, 'BCC',NULL
EXEC @hr = sp_OASetProperty @MailID, 'MailFormat', 0 -- MIME format to ensure attachments are sent correctly via internet
EXEC @hr = sp_OAMethod @MailID, 'AttachFile', NULL, @Attachment
EXEC @hr = sp_OAMethod @MailID, 'AttachFile', NULL, @Attachment1
EXEC @hr = sp_OASetProperty @MailID, 'bodyformat', 0
EXEC @hr = sp_OAMethod @MailID, 'Send', NULL
EXEC @hr = sp_OADestroy @MailID
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources