Sending Email Through SQL Server (Part-2)

If you’re an aspiring learner and you haven’t been through the previous part, please click here. If you are already aware of the basic implementation, read on.

8) Send a mail in a simple text format based on a query with clauses.
USE msdb
GO

DECLARE
@iDeptID INT,
@SQL NVARCHAR(MAX),
@Tab char(1)=char(9)

SELECT @iDeptID = MAX(DeptNo) FROM TestDB.dbo.Dept WITH (NOLOCK)
SELECT @SQL =
‘ SELECT TOP 5 [EmpID],[Ename],[Salary],[Dname]
FROM [TestDB].[dbo].[Emp] a WITH (NOLOCK)
JOIN [TestDB].[dbo].[Dept] b ON a.DeptID = b.DeptID WHERE DeptNo=’
+CAST(@iDeptID AS NVARCHAR(2))
+’ ORDER BY EmpID DESC’

EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘Mail_Profile’,
@recipients = ‘contact@bigdatansql’,
@query=@SQL,
@subject=’Top 5 Employee Records’,
@attach_query_result_as_file = 1,
@query_attachment_filename = ‘EmpRecords.csv’,
@query_result_separator=@Tab,
@query_result_width =3767,
@query_result_no_padding=1,
@query_result_header = 0

Explanation:
@attach_query_result_as_file Specifies whether the result set of the query is returned as an attached file. attach_query_result_as_file is of type bit, with a default of 0.

When the value is 0, the query results are included in the body of the e-mail message, after the contents of the @body parameter. When the value is 1, the results are returned as an attachment. This parameter is only applicable if @query is specified. Specifies the file name to use for the result set of the query attachment. query_attachment_filename is of type nvarchar(255), with a default of NULL. This parameter is ignored when attach_query_result is 0. When attach_query_result is 1 and this parameter is NULL, Database Mail creates an arbitrary filename.

@query_attachment_filename Specifies the file name to use for the result set of the query attachment. query_attachment_filename is of type nvarchar(255), with a default of NULL. This parameter is ignored when attach_query_result is 0. When attach_query_result is 1 and this parameter is NULL, Database Mail creates an arbitrary filename.

@query_result_separator is the character used to separate columns in the query output. The separator is of type char(1). Defaults to ‘ ‘ (space).
@Tab variable is holding the tab separator/delimiter.

9) Send an email with a conditional clause.
IF EXISTS (
SELECT COUNT(0)
FROM TestDB.dbo.Emp
WHERE DeptNo = 10 )
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘Mail_Profile’,
@recipients = ‘admin@bigdatansql’,
@query = ‘SELECT EmpNo, Ename, Job, Sal+ISNULL(Comm,0) As Salary FROM Emp’,
@body = ‘ Employees Report ‘,
@subject = ‘Employees Report’,
@body_format = ‘HTML’
END

10) Send the query results in a HTML Tabular format.