SQL Server – Convert results from query to string and send email from DB

Given below is an example of how to convert data from SQL server into a string and send the results in an email from database. This can be used to send scheduled reports. Also there is no need to create an executable in c# or any other language to send email reports in the below example.

  

/*Create temp table to store data.*/

DECLARE @temp TABLE (emp_id int,emp_name varchar(10))

INSERT INTO @temp (emp_id,emp_name) VALUES(1,'John')

INSERT INTO @temp (emp_id,emp_name) VALUES(2,'Tom')

DECLARE @result VARCHAR(4000)

/*Get results into a string*/

SELECT @result =

       (

       SELECT convert(varchar, emp_id)+','+emp_name+'\n'

       FROM @temp

       FOR XML PATH('')

       )

SELECT @result

/*Send the report to recipients*/

EXEC msdb..sp_send_dbmail @profile_name='TEST',

@recipients='someone@somewhere.com',

@subject='DB Job',

@body_format = 'HTML',

@body= @result