'Format sql mail with html

I'm trying to formt an html mail that contains a set of rows. This is my statement:

use NDOCS
go
begin 
if not exists (select doc_number, Var_descrizione, convert(date, CREATION_DATE, 23) as Data_Import_fattura
from docsadm.DPA_DIAGRAMMI
join docsadm.PROFILE
on docnumber = doc_number 
join docsadm.DPA_STATI s
on id_stato = s.SYSTEM_ID
where creation_date between DATEADD (DD,-50,getdate()) and DATEADD (DD,-1, getdate()) and id_stato in (15,12,9))
begin 
declare @htmlbody varchar(500)
set @htmlbody = '<!DOCTYPE html>
<html>
<head>
<style>

p {
  font-family: verdana;
  font-size: 15px;
  text-align: center;
}
</style>
</head>
<body>
<p>Tutte le fatture passive risultano correttamente inviate a SAP. 
Sono state prese in considerazione le fatture scaricate da casella negli ultimi 3 giorni</p>
</body>
</html>'

EXEC MSDB.DBO.sp_send_dbmail
@PROFILE_NAME ='InfoMessage',
@recipients ='stefano.pinoli@BLA',
@subject ='[INFO] Report controllo invio fatture a SAP',
@body = @htmlbody,
@body_format = 'html',
@attach_query_result_as_file =0

end 
else
use NTTDOCS
begin 
if  exists (select doc_number, Var_descrizione, convert(date, CREATION_DATE, 23) as Data_Import_fattura
from docsadm.DPA_DIAGRAMMI
join docsadm.PROFILE
on docnumber = doc_number 
join docsadm.DPA_STATI s
on id_stato = s.SYSTEM_ID
where creation_date between DATEADD (DD,-50,getdate()) and DATEADD (DD,-1, getdate()) and id_stato in (15,12,9))
begin 

declare @htmlbody2 nvarchar(max),
        @NumeroDocumento varchar(50),
        @DataCreazioneDocumento varchar(50),
        @StatoDocumento varchar(50),
        @sql nvarchar(max)
set @sql =''

set @htmlbody2 =N'<html><head><title>ReportDocsPA</title></head><body><center><h1>ReportDocsPA</h1></center>
<center><table><th>Docnumber</th><th>Dta_creazione</th><th>Stato_doc</th>'



declare system_table cursor 
read_only
for
select doc_number, Var_descrizione, convert(date, CREATION_DATE, 23) as Data_Import_fattura
from docsadm.DPA_DIAGRAMMI
join docsadm.PROFILE
on docnumber = doc_number 
join docsadm.DPA_STATI s
on id_stato = s.SYSTEM_ID
where creation_date between DATEADD (DD,-50,getdate()) and DATEADD (DD,-1, getdate()) and id_stato in (15,12,9)

open system_table
fetch next from system_table into @NumeroDocumento, @DataCreazioneDocumento, @StatoDocumento
while @@FETCH_STATUS <>-1
begin 
set @sql = @htmlbody2 + @sql +N'<TR><TD>'+ @NumeroDocumento + '</TD><TD>'+ @DataCreazioneDocumento +'</TD><TD>'+ @StatoDocumento +'</TD></TR>'

fetch next from system_table into @NumeroDocumento, @DataCreazioneDocumento, @StatoDocumento
end
close system_table
deallocate system_table

set @htmlbody2= @sql + N'</table></body></html>'
EXEC MSDB.DBO.sp_send_dbmail
@PROFILE_NAME ='InfoMessage',
@recipients ='stefano.pinoli@BLA',
@subject ='[WARNING] Report controllo invio fatture a SAP',
@body = @htmlbody2,
@body_format = 'html',
@attach_query_result_as_file = 0
end
end
end

It should perform a query, and:

if there are not results, stamps a message into a mail (there are not invoices...)
if there are results, create a table into the mail with the results.

My problem is that I can not format the table. Following the statement above, I've this grotesque output:

enter image description here

Can you tell my where is the problem and how can I resolve it..? Thanks Stefano

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source

'Format sql mail with html

Sources

Related Questions