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'
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;
16 People are following this question.
