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.

<p class="has-text-align-justify" value="<amp-fit-text layout="fixed-height" min-font-size="6" max-font-size="72" height="80">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. <br><br><strong>@query_attachment_filename</strong> 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.<br><br><strong>@query_result_separator</strong> is the character used to separate columns in the query output. The separator is of type char(1). Defaults to ' ' (space). <br>@Tab variable is holding the tab separator/delimiter.<br><br><strong>9) Send an email with a conditional clause.</strong><br><span style="color:#0c1eeb;" class="has-inline-color">IF EXISTS (<br> SELECT COUNT(0)<br> FROM TestDB.dbo.Emp<br> WHERE DeptNo = 10 )<br>BEGIN<br> EXEC msdb.dbo.sp_send_dbmail<br> @profile_name = 'Mail_Profile',<br> @recipients = 'admin@bigdatansql',<br> @query = 'SELECT EmpNo, Ename, Job, Sal+ISNULL(Comm,0) As Salary FROM Emp',<br> @body = ' <strong><em>Employees Report</em></strong> ',<br> @subject = 'Employees Report',<br> @body_format = 'HTML'<br>END</span><br><br><strong>10) Send the query results in a HTML Tabular format.<br></strong><br><img class="wp-image-1469" style="width:800px;" src="https://bigdatansql.files.wordpress.com/2020/10/dbmail_html.jpg&quot; alt=""><br><br><a href="https://gist.github.com/sqlbankblogspot/baa1b1902a132ae78aaa0585e20ba2c7"><strong>Click here for the code.</strong></a><br><br>The result of the code is an email with the employee data in a tabular format as shown below.<br><br><img class="wp-image-1470" style="width:400px;" src="https://bigdatansql.files.wordpress.com/2020/10/email.jpg&quot; alt=""><br><br><a href="https://bigdatansql.com/2020/10/24/sending-email-through-sql-server-part-3/&quot; target="_blank" rel="noreferrer noopener">Click here for the next part</a>.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.

@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.



Click here for the code.

The result of the code is an email with the employee data in a tabular format as shown below.



Click here for the next part.

4 comments

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s