Wstawianie query do treści maila html

0

Cześć,
znalazłem temat z wysyłanie maili html z poziomu SQL.
https://www.sqlservercentral.com/Forums/Topic1465444-279-1.aspx
Podane przykłady wyglądają tak, że w treści maila jest najpierw tekst a później query. Potrzebowałbym wpleść query (wynikiem będzie zawsze jedna komórka) w treść maila.

próbowałem coś takiego, ale nie działa

'<H1 style="color: #000000">HEADER ' + (select top 1 ID from ORDERS) + ' OF TABLE</H1>' +

będę wdzięczny za informację jak powinno to wyglądać.

-- Email Querry--
DECLARE @Body varchar(max)
declare @TableHead varchar(max)
declare @TableTail varchar(max)
declare @mailitem_id as int
declare @statusMsg as varchar(max)
declare @Error as varchar(max) 
declare @Note as varchar(max)

Set NoCount On;
set @mailitem_id = null
set @statusMsg = null
set @Error = null
set @Note = null
Set @TableTail = '</table></body></html>';

--HTML layout--
Set @TableHead = '<html><head>' +
'<H1 style="color: #000000">HEADER OF TABLE</H1>' +
'<style>' +
'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:9pt;color:Black;} ' +
'</style>' +
'</head>' +
'<body><table cellpadding=0 cellspacing=0 border=0>' +
'<tr bgcolor=#F6AC5D>'+
'<td align=center><b>Name of column</b></td>' +
'<td align=center><b>Name of column</b></td>' +
'<td align=center><b>Name of column</b></td>' +
'<td align=center><b>Name of column</b></td>' + 
'<td align=center><b>Name of column</b></td></tr>';

--Select information for the Report-- 
Select @Body= (Select 
Column As [TD],
Column As [TD],
Column As [TD],
Column As [TD],
Column As [TD]

FROM [DB].[dbo].[Table] 
where -condition-
(whatever you want to do else ...)

For XML raw('tr'), Elements)

-- Replace the entity codes and row numbers
Set @Body = Replace(@Body, '_x0020_', space(1))
Set @Body = Replace(@Body, '_x003D_', '=')
Set @Body = Replace(@Body, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')
Set @Body = Replace(@Body, '<TRRow>0</TRRow>', '')


Set @Body = @TableHead + @Body + @TableTail

-- return output--
Select @Body

--Email
EXEC msdb.dbo.sp_send_dbmail 
@profile_name =email', <--This is the mail account to sent from. 
@mailitem_id = @mailitem_id out,
@recipients='[email protected]',
@subject = 'subject Email',
@body = @Body,
0
declare @h1 as varchar(max)
SELECT top 1 @h1= '<H1 style="color: #000000">HEADER ' +convert(varchar(max),ID) + ' OF TABLE</H1>' 
FROM ORDERS

SET @TableHead = '<html><head>' +
@h1 +
'<style>' +
'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:9pt;color:Black;} ' +
'</style>' +
'</head>' +
'<body><table cellpadding=0 cellspacing=0 border=0>' +
'<tr bgcolor=#F6AC5D>'+
'<td align=center><b>Name of column</b></td>' +
'<td align=center><b>Name of column</b></td>' +
'<td align=center><b>Name of column</b></td>' +
'<td align=center><b>Name of column</b></td>' + 
'<td align=center><b>Name of column</b></td></tr>';```

1 użytkowników online, w tym zalogowanych: 0, gości: 1