--SQL HTML email as a table
SQL HTML email as a table
create table #tmpChadRocksTable (
SystemName NVARCHAR(255),
SubjectName NVARCHAR(MAX),
ExpirationDateString NVARCHAR(MAX),
RD INT)
INSERT INTO #tmpChadRocksTable
exec chadrocks.dbo.AlertOnCerts
select * FROM #tmpChadRocksTable
DECLARE @sBody NVARCHAR(MAX)
DECLARE @Table NVARCHAR(MAX)
SET @sBody = CAST(( SELECT SystemName AS 'td','',SubjectName AS 'td','', ExpirationDateString AS 'td','', RD AS 'td',''
FROM #tmpChadRocksTable
ORDER BY SystemName
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
SET @Table ='
<table border = 1>
<tr>
<th>Name</th><th>NPI Number</th><th>CompletedDate</th></tr>'
SET @sBody = @Table +@sBody +'</table>'
print @sBody
DROP TABLE #tmpChadRocksTable