vendredi 19 juin 2015

Can someone help me with Formatted HTML Table with TSQL

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