html sql email and body formatting 1

dvannoy · Sep 28, 2016

I'm using the below code to send out an html email using sql server 2012. I'm trying to include the @count inside the html message but can't get it to work. I've tried (' + @count +') as well.

Code:

Declare @count int = 0
DECLARE @Body NVARCHAR(2000)

SELECT @count = COUNT(1) FROM MY_TABLE
WHERE DATE < GETDATE() -7
AND
STATUS = 'OPEN'

IF @count > 0
BEGIN
SET @Body=N'
<html>
 <head>
  <meta http-equiv=Content-Type content="text/html; charset=windows-1252">
  <style>p{font-size:11.0pt;font-family:Calibri,"Arial","serif";}</style>
 </head>
 <body>
 <p>Hello All,</p>
 <p><b>This is an automated message</b>,<br />
 <br />
  You Have '+ @count +' Open Orders</p>
<p>My message</p>
<br />
 <p>Thank you<br />
 </p>
 </body>
</html>'
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'My Porfile',
    @recipients = 'MyEmail',
    @subject = 'My Subject',
    @body = @Body,
	@body_format = 'HTML'
	
END

any help would be appreciated

thanks

bborissov · Sep 29, 2016

You can not concatenate string and integer.
You must CAST @count to nvarchar first:

Code:

...
  You Have '+ CAST(@count as nvarchar(300))+' Open Orders
...

Borislav Borissov
VFP9 SP2, SQL Server

dvannoy · Sep 29, 2016

Thank you....

I'm trying to modify this to include a list of Open Orders. Not sure how to do that. I'm already using a select to count the records. how would I add another select to include the data of the counted records ?

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

html sql email and body formatting 1

dvannoy

MIS

bborissov

Programmer

dvannoy

MIS

Similar threads

Part and Inventory Search

Sponsor