question

zillabaug avatar image
zillabaug asked

I need help with Email Formatted HTML Table with T-SQL

I created a SQL server job with query ( as shown below) that dynamical retrieve set of data, generates html table and email it to a recipient. The Job work fine as long as the query returns a record set which renders the html tableas shown in the attached image.On the contrary, when the query returns no record set, the recipient gets a blank email instead of a table with default values or null values. Does anyone know how to tweak this code to render html table with default value of zeros whenever the query returns no record set? use dev; declare @tableHTML nvarchar(max); set @tableHTML =N'

' + 'The Exams Attempt for the month of' + ' ' ` ` + DATENAME(MONTH, (DATEADD(MONTH,-1,DATEADD(month, DATEDIFF(month, 0, ![alt text][1]getdate()), 0))) ) + '

' + '' + '' + '' + N' ' + ' ' + '3rd Attempt' + ' '+ N' app_lvl' + N' EligAtt' + N' Category' + N' Result' + N' Count' + CAST ( ( SELECT td = sc_pracrslts.reg_lvl, '',td = sc_pracrslts.elig_attmpt_no, '',td = sc_pracrslts.category, '',td = sc_pracrslts.pass_ind, '',td = count(*), '' FROM ARS_copy..sc_pracrslts WHERE exam_dt >= '4/1/2015' and exam_dt < '5/1/2015' AND sc_pracrslts.elig_attmpt_no = 3 and sc_pracrslts.category = '1' GROUP BY sc_pracrslts.reg_lvl, sc_pracrslts.category, sc_pracrslts.elig_attmpt_no, sc_pracrslts.pass_ind ORDER BY sc_pracrslts.reg_lvl, sc_pracrslts.category, sc_pracrslts.elig_attmpt_no, sc_pracrslts.pass_ind FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) + N' ' + '' + '' + ''; EXEC msdb.dbo.sp_send_dbmail @ recipients=N'aboamah@ABC.org', @subject = 'Exams Attempt Dashboard ', @body = @tableHTML, @body_format = 'HTML' , @profile_name='Augie Bomah' [1]: /storage/temp/2515-capture.jpg
sql-server-2005tsqldynamic-sqldatabase-emailhtml
capture.jpg (18.8 KiB)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Shawn_Melton avatar image
Shawn_Melton answered
Just try something like this: CREATE TABLE #test1 ( col1 int, col2 varchar(2), col3 varchar(3) ) DECLARE @tableHTML nvarchar(max) SET @tableHTML = N'

' + N'The exams attempt for the month of' + '

' + '' + N'' + N' 3rd Attempt' + N'col1' + N'col2' + N'col3' IF EXISTS (SELECT TOP 1 * FROM #test1) SET @tableHTML += CAST((SELECT td = col1, '', td = col2, '', td=col3,'' FROM (SELECT col1,col2,col3 FROM #test1) AS d FOR XML PATH('tr'), TYPE) AS nvarchar(max)) ELSE SET @tableHTML += 'NULLNULLNULL' + N'' DROP TABLE #test1;
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Shawn, your approach didn't work..
0 Likes 0 ·
@zillabaug updated anwser
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.