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'<H3><font color="Red">' +
'The Exams Attempt for the month of' + ' '
` ` + DATENAME(MONTH, (DATEADD(MONTH,-1,DATEADD(month, DATEDIFF(month, 0, ![alt text][1]getdate()), 0))) ) + '</H3>' +
'<td>' + N'<table border="1">' +
'<caption>' + '3rd Attempt' + '</caption>'+
N'<tr><th>app_lvl</th>' +
N'<th>EligAtt</th>' +
N'<th>Category</th>' +
N'<th>Result</th>' +
N'<th>Count</th>' +
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'</table>' +
'</td>' +
'</tr>' + '</table>';
EXEC msdb.dbo.sp_send_dbmail
@recipients=N'aboamah@ABC.org',
@subject = 'Exams Attempt Dashboard ',
@body = @tableHTML,
@body_format = 'HTML' ,
@profile_name='Augie Bomah'
I tried the ISNULL on each column and put some value. Like:
SELECT td = ISNULL(sc_pracrslts.reg_lvl, 'NULL') ,
'' ,
![enter image description here][1] td = ISNULL(sc_pracrslts.elig_attmpt_no, 'NULL') ,
'' ,
td = ISNULL(sc_pracrslts.category, 'NULL') ,
'' ,
td = ISNULL(sc_pracrslts.pass_ind, 'NULL') ,
'' ,
td = COUNT(*) ,
but it didn't work !!
Aucun commentaire:
Enregistrer un commentaire