how to send a query result as an email body sql server

Solutions on MaxInterview for how to send a query result as an email body sql server by the best coders in the world

showing results for - "how to send a query result as an email body sql server"

15 Jan 2020

1CREATE TABLE #Temp 
2( 
3  [Rank]  [int],
4  [Player Name]  [varchar](128),
5  [Ranking Points] [int],
6  [Country]  [varchar](128)
7)
8
9INSERT INTO #Temp
10SELECT 1,'Rafael Nadal',12390,'Spain'
11UNION ALL
12SELECT 2,'Roger Federer',7965,'Switzerland'
13UNION ALL
14SELECT 3,'Novak Djokovic',7880,'Serbia'
15
16DECLARE @xml NVARCHAR(MAX)
17DECLARE @body NVARCHAR(MAX)
18
19SET @xml = CAST(( SELECT [Rank] AS 'td','',[Player Name] AS 'td','', [Ranking Points] AS 'td','', Country AS 'td'
20FROM #Temp 
21ORDER BY Rank 
22FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
23
24SET @body ='<html><body><H3>Tennis Rankings Info</H3>
25<table border = 1> 
26<tr>
27<th> Rank </th> <th> Player Name </th> <th> Ranking Points </th> <th> Country </th></tr>'    
28
29SET @body = @body + @xml +'</table></body></html>'
30
31EXEC msdb.dbo.sp_send_dbmail
32@profile_name = 'SQL ALERTING', -- replace with your SQL Database Mail Profile 
33@body = @body,
34@body_format ='HTML',
35@recipients = 'bruhaspathy@hotmail.com', -- replace with your email address
36@subject = 'E-mail in Tabular Format' ;
37
38DROP TABLE #Temp

source