Tuesday, September 6, 2016

Sending Email in HTML Table Format Using TSQL in SQL Server

As we know that SQL language is specified as an ANSI and ISO standard and performance, scalability, and optimisation are important for database-driven applications, especially on the Web.  In application development, we are required to build a SQL program to perform certain task(s) on periodic basis or daily basis. For example - send an email alert with Department Wise Employee List from SQL Server to business users. Let us discuss how we can send an email in HTML table format using TSQL.

It is important to us before sending emails from SQL Server, we should have the following things-

To know all the above steps and their configuration, you can visit Enabling and configuring Database Mail in SQL Server using T-SQL

To understand this functionality, we can use the following Employee table structure to generate the tabular html list as given below: 
---- Create data table Employee
CREATE TABLE Employee
(
EmpId INT,
EmpName VARCHAR(25),
Department VARCHAR(25)
)

---- Insert Data into Employee
INSERT INTO Employee (EmpId,EmpName,Department)
VALUES (1, 'Ryan Arjun','Finance'),
(2, 'Kimmy Wang','Admin'),
(3, 'Lucy Gray','Sales'),
(4, 'Billy Doug','Admin'),
(5, 'Gery Dean','IT')

----Pull the data from Employee
SELECT EmpId,EmpName,Department
FROM DBO.Employee
EmpId
EmpName
Department
1
Ryan Arjun
Finance
2
Kimmy Wang
Admin
3
Lucy Gray
Sales
4
Billy Doug
Admin
5
Gery Dean
IT
As we know that SQL Server provides msdb.dbo.sp_send_dbmail stored procedure to send emails to the business users and this stored procedure requires some parameters such as-
---- call SP_SEND_DBMAIL to send alerts
EXEC msdb.dbo.sp_send_dbmail
----SQL Email Profiler
@profile_name = @inpEmailProfiler,
----Who will receive the emails
@recipients = @inpToEmail,
----Type of format plain/html
@body_format=@inpBodyFormat,
---- Tabular design of data in html format
@body = @body,
---- Subject Line of the email
@subject = @inpSubjectLine;
How to create HTML table header
So, before creating the html tabular format, we should declare these variables and assign values in our T-SQL code as given below:
----- Declare local variables
----- Table header variable
declare @HtmlHeader nvarchar(500),
----- Table Body variable
@body nvarchar(max),
----SQL Email Profiler
@inpEmailProfiler VARCHAR(50) ='DBANotifications',
----Who will receive the emails
@inpToEmail VARCHAR(50)='youremail@xyz.com',
----Type of format plain/html
@inpBodyFormat VARCHAR(5) ='HTML',
---- Subject Line of the email
@inpSubjectLine NVARCHAR(200)='Department Wise Employee List';

----- HTML based tabular header
set @HtmlHeader='<html><style>table, th, td {border: 1px solid black;}</style>
<body width=400pt>
<table cellpadding=0 cellspacing=0 width=400pt>
  <tr>
  <td colspan=3 width=400 bgcolor="#ff9966" align="center"><B>Department Wise Employee List</B></td>
 </tr>
 <tr align="center">
  <td>Emp Code</td>
  <td>Employee Name</td>
  <td>Department</td>
 </tr>'
 ---- print table header variable
 Print @HtmlHeader

---- output of the header variable
<html><style>table, th, td {border: 1px solid black;}</style>
<body width=400pt>
<table cellpadding=0 cellspacing=0 width=400pt>
  <tr>
  <td colspan=3 width=400 bgcolor="#ff9966" align="center"><B>Department Wise Employee List</B></td>
 </tr>
 <tr align="center">
  <td>Emp Code</td>
  <td>Employee Name</td>
  <td>Department</td>
 </tr>
---- copy and paste the above output in MS Excel and see how it look like in email body
Department Wise Employee List
Emp Code
Employee Name
Department
How to create HTML table body
We have created our table header and need to populate the table body elements which will be pulled from our data table. To pull the data from database, we are using the CAST function of SQL Server as given below:
----- Generate HTML Table body from the database table
 ---- and set value in the local @body variable
  SET @body = CAST( (
  SELECT TD =CONVERT(VARCHAR(4),EmpId)+'</TD><TD>' + EmpName+'</TD><TD>' +Department
  FROM DBO.Employee
  FOR XML PATH('TR'), TYPE) AS VARCHAR(MAX));

  ---- concate html body header and body variable here
  set @body= @HtmlHeader+@body+ '</table></body></html><br>'
  ---- Replace <> tags here
  set @body=Replace(Replace(@body,'&lt;','<'),'&gt;','>')

  ---- Print body variable
  Print @body

---- output of the @body variable
<html><style>table, th, td {border: 1px solid black;}</style>
<body width=400pt>
<table cellpadding=0 cellspacing=0 width=400pt>
  <tr>
  <td colspan=3 width=400 bgcolor="#ff9966" align="center"><B>Department Wise Employee List</B></td>
 </tr>
 <tr align="center">
  <td>Emp Code</td>
  <td>Employee Name</td>
  <td>Department</td>
 </tr><TR><TD>1</TD><TD>Ryan Arjun</TD><TD>Finance</TD></TR><TR><TD>2</TD><TD>Kimmy Wang</TD><TD>Admin</TD></TR><TR><TD>3</TD><TD>Lucy Gray</TD><TD>Sales</TD></TR><TR><TD>4</TD><TD>Billy Doug</TD><TD>Admin</TD></TR><TR><TD>5</TD><TD>Gery Dean</TD><TD>IT</TD></TR></table></body></html><br>

 ---- copy and paste the above output in MS Excel and see how it look like in email body
Department Wise Employee List
Emp Code
Employee Name
Department
1
Ryan Arjun
Finance
2
Kimmy Wang
Admin
3
Lucy Gray
Sales
4
Billy Doug
Admin
5
Gery Dean
IT
Send Email Alerts from SQL Server
We have already created our tabular html body and set the necessary values in the requested parameters. We are going to use msdb.dbo.sp_send_dbmail stored procedure to send emails to the business users with requested parameters such as-
---- call SP_SEND_DBMAIL to send alerts
EXEC msdb.dbo.sp_send_dbmail
----SQL Email Profiler
@profile_name = @inpEmailProfiler,
----Who will receive the emails
@recipients = @inpToEmail,
----Type of format plain/html
@body_format=@inpBodyFormat,
---- Tabular design of data in html format
@body = @body,
---- Subject Line of the email
@subject = @inpSubjectLine;

After executing the above script, you will get the email notification in your email inbox and it would be look like as –

T-SQL Code to send email alerts in a Glance :
----- Declare local variables
----- Table header variable
declare @HtmlHeader nvarchar(500),
----- Table Body variable
@body nvarchar(max),
----SQL Email Profiler
@inpEmailProfiler VARCHAR(50) ='DBANotifications',
----Who will receive the emails
@inpToEmail VARCHAR(50)='youremail@xyz.com',
----Type of format plain/html
@inpBodyFormat VARCHAR(5) ='HTML',
---- Subject Line of the email
@inpSubjectLine NVARCHAR(200)='Department Wise Employee List';

----- HTML based tabular header
set @HtmlHeader='<html><style>table, th, td {border: 1px solid black;}</style>
<body width=400pt>
<table cellpadding=0 cellspacing=0 width=400pt>
  <tr>
  <td colspan=3 width=400 bgcolor="#ff9966" align="center"><B>Department Wise Employee List</B></td>
 </tr>
 <tr align="center">
  <td>Emp Code</td>
  <td>Employee Name</td>
  <td>Department</td>
 </tr>'
 ---- print table header
 Print @HtmlHeader

 ----- Generate HTML Table body from the database table
 ---- and set value in the local @body variable
  SET @body = CAST( (
  SELECT TD =CONVERT(VARCHAR(4),EmpId)+'</TD><TD>' +   EmpName+'</TD><TD>' +Department
  FROM DBO.Employee
  FOR XML PATH('TR'), TYPE) AS VARCHAR(MAX));

  ---- concate html body header and body variable here
  set @body= @HtmlHeader+@body+ '</table></body></html><br>'
  ---- Replace <> tags here
  set @body=Replace(Replace(@body,'&lt;','<'),'&gt;','>')

  ---- Print body variable
  Print @body

  ---- call SP_SEND_DBMAIL to send alerts
EXEC msdb.dbo.sp_send_dbmail
----SQL Email Profiler
@profile_name = @inpEmailProfiler,
----Who will receive the emails
@recipients = @inpToEmail,
----Type of format plain/html
@body_format=@inpBodyFormat,
---- Tabular design of data in html format
@body = @body,
---- Subject Line of the email
@subject = @inpSubjectLine;

Conclusion

To enable your database email through T-SQL codes, you should have the sysadmin rights. This code is very useful especially when you’re setting up Database Mail for multiple instances and multiple SMTP servers. You can create store procedure to send various emails from multiple SQL instances which makes your life easier to automate these email alerts.

7 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. Thanks for this Article. I tried the same and its working fine.

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete
  4. This comment has been removed by the author.

    ReplyDelete
  5. If you just posted a new website, but you do not have a HTML email form uploaded and you need to quickly provide a way for your visitors to contact you, then you should simply post your email address on your Contact page. Web Design Company

    ReplyDelete
  6. Web templates are created keeping the above three requirements in mind. website templates html5

    ReplyDelete
  7. How to make the first colum empid right justified ??

    ReplyDelete

Popular Posts