- - Generate HTML output and send result by email Query--
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='abc@test.com.qa',
@subject = 'subject Email',
@body = @Body,
@body_format = 'HTML';
Like this:
Like Loading...
Related
SimpleSQLDBA | Shadab Mohammad 