Query Result in HTML format

Question

Post reply

Query Result in HTML format

pawana.paul

SSCommitted

Points: 1686
More actions
August 28, 2012 at 11:57 am

#261181

Hello All,
I am using the below script to generate report in HTML format for the query. If there is no result found for today, I am getting blank body.
Please let me know how to get columnName1... columnName4 if there are no results from the query.
Thanks in advance.
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<H1>Heading</H1>' +
N'<table border="5">' +
N'<tr><th>ColumnName1</th><th>ColumnName2</th><th>ColumnName3</th>' +
N'<th>ColumnName4</th><th>ColumnName5</th></tr>' +
CAST ( ( SELECT td = ColumnName1, '',
td = ColumnName2, '',
td = ColumnName3, '',
td = ColumnName4, '',
td = ColumnName5, ''
FROM TABLE 1, TABLE 2, ...........
WHERE Condition
AND (
(DAY(GDM.DATE_CREATED) = DAY(GETDATE() ))
AND
(MONTH(GDM.DATE_CREATED) = MONTH(GETDATE() ) )
AND
(YEAR(GDM.DATE_CREATED) = YEAR(GETDATE() ))
)
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
EXEC msdb.dbo.sp_send_dbmail @recipients="recipients@123.com",
@subject = 'Subject Line',
@body = @tableHTML,
@body_format = 'HTML' ;

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

~~Divide and Conquer.~~

~~Divide your html into 3 parts: headers, rows and closure.~~

OK, I misread. All you have to do is add an ISNULL outside the CAST.

However, the original suggestion is a good practice that can give you code that's easier to test.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Lowell SSC Guru Points: 323518 More actions · Answer 2

I always break it into peices;

the Header and Tail are appended to the body;

then i can test of the Body is an empty string, i can convert it to "No data found" or something.

Declare @HTMLBody varchar(max),

@TableHead varchar(max),

@TableTail varchar(max)

Set NoCount On;

Set @TableTail = '</table></body></html>';

Set @TableHead = '<html><head>' +

'<style>' +

'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;} ' +

'</style>' +

'</head>' +

'<body><table cellpadding=0 cellspacing=0 border=0>' +

'<tr bgcolor=#FFEFD8><td align=center><b>Server Name</b></td>' +

'<td align=center><b>Product</b></td>' +

'<td align=center><b>Provider</b></td>' +

'<td align=center><b>Data Source</b></td>' +

'<td align=center><b>Is Linked?</b></td></tr>';

Select @HTMLBody = (Select Row_Number() Over(Order By is_linked, name) % 2 As [TRRow],

name As

,

product As

,

provider As

,

data_source As

,

is_linked As

From sys.servers

Order By is_linked, name

For XML raw('tr'), Elements)

IF @HTMLBody IS NULL OR @HTMLBody = ''

SET @HTMLBody = '<tr><td colspan="4">no data found</td></tr>'

Select @HTMLBody = @TableHead + @HTMLBody + @TableTail

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!