After long time jolting something worth having glance. In application development we often come across a typical requirement where we are required to build a utility they will perform certain task(s) on periodic basis. One such type of common requirement is to shot mail(s) on periodic basis.
Recently I built one such utility for auto mailer. There were three (3) possible options to achieve this viz:
- Schedule an .exe application which will send emails
- Write an window service
- Create sql job and execute a sproc which will send email
Of these three options, last option has upper hand, as the data that required to be mailed was in database, also the sender email address and receiver email address were available in db and lastly there is no extra installer required since the job is defined in sql server and hence no deployment hiccups.
But the challenge was to build a dynamic HTML formatted email (challenge: because I underestimated send mail sproc of sqlserver).
But then bit of googling cleared my misconception and then I kick started building the auto mailer.
- The rough layout of the desired html is as below:
Spot Nomination for July 2012
|1||Joe Gracia||Mark twain||USA||1000||USD||Cash|
|2||Narayan Jadhav||Sam Powel||USA||1000||USD||Cash|
|3||Manish Patil||Jignesh Patel||India||4000||INR||Cash|
|4||Amy Wang||Lu Sang||Taiwan||2000||TD||Cash|
|5||Tsai Wu||Sam Mochi||Taiwan||2000||TD||Cash|
|6||Juan Marcos||Anna Alvaris||Spain||1000||EURO||Cash|
|7||Adongo Wangai||Tobe Mugambe||Kenya||4000||KES||Cash|
|8||Akae Takahashi||Chi Itou||Japan||2000||YEN||Cash|
|9||Mohit Gupta||Ashutosh Singh||India||4000||INR||Cash|
Thanks & Regards,
Payroll Management Team
- The Logic for the Stored Procedure
- Create a temp table and define necessary columns in it
- Populate the temp table by inserting the required data into it
- Declare two variables viz @xml NVARCHAR(MAX); @body NVARCHAR(MAX);
- Extract the data from temp table, cast it to xml string (essentially with html tags in it) and then assign it to @xml variable
- Now this @xml variable is having partial, table formatted data of the body tag
- Now build the remaining part of the body tag
- At concluding part just execute the msdb.dbo.sp_mail_dbmail stored procedure and provide the @body variable to it
- The complete sql script is as below:
CREATE PROCEDURE [dbo].[SendEmail_Date_AwardType_Location] @FromDate DateTime , @ToDate DateTime , @AwardTypeID Int , @LocationName Varchar(50) AS BEGIN SET NOCOUNT ON; CREATE TABLE #SpotNomApporval ( [EmpID] INT ,[EmployeeName] NVARCHAR(150) ,[Payroll] NVARCHAR(150) ,[ApprovingAuthority] NVARCHAR(150) ,[Amount] INT ,[Currency] NVARCHAR(150) ,[Mode] NVARCHAR(150) ) INSERT INTO #SpotNomApporval SELECT [EmpId] ,[EmpName] AS [EmployeeName] ,[ApprovingAuthority] , AI.[GlobalLocation] AS [Payroll] ,[Amount] ,[Currency] ,[Mode] FROM [AwardInfo] AS AI LEFT JOIN [AwardType] AS AT ON AI.AwardTypeID = AT.AwardTypeId LEFT JOIN [AwardLocation] AS AL ON AI.GlobalLocation = AL.LocationName WHERE AT.AwardTypeId = @AwardTypeID AND AL.LocationName = @LocationName AND DateofRequisition BETWEEN @FromDate AND @ToDate DECLARE @xml NVARCHAR(MAX); DECLARE @body NVARCHAR(MAX); DECLARE @duration NVARCHAR(100); SET @xml = CAST(( SELECT [EmpId] AS 'td' ,'',[EmployeeName] AS 'td' ,'',[ApprovingAuthority] AS 'td' ,'',[Payroll] AS 'td' ,'',[Amount] AS 'td' ,'',[Currency] AS 'td' ,'',[Mode] AS 'td' FROM #SpotNomApporval FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX)) SET @duration = (SELECT Month = CASE MONTH(GETDATE()) WHEN 1 THEN 'January' WHEN 2 THEN 'February' WHEN 3 THEN 'March' WHEN 4 THEN 'April' WHEN 5 THEN 'May' WHEN 6 THEN 'June' WHEN 7 THEN 'July' WHEN 8 THEN 'August' WHEN 9 THEN 'September' WHEN 10 THEN 'Octomber' WHEN 11 THEN 'November' WHEN 12 THEN 'December' END) +' '+ CAST((SELECT "Year" = YEAR(GETDATE())) AS VARCHAR(5)) SET @body ='<html><body><span style="color:#17365d"><H3>Spot Nomination for '+@duration+'</H3></span> <table border = 1> <tr> <td bgcolor="#6B696B"> Emp.ID </td> <td bgcolor="#6B696B"> Employee Name </td> <td bgcolor="#6B696B"> Approver </td> <td bgcolor="#6B696B"> Payroll </td> <td bgcolor="#6B696B"> Amount </td> <td bgcolor="#6B696B"> Currency </td> <td bgcolor="#6B696B"> Mode </td></tr>' SET @body = @body + @xml +'</table></body></html>' SET @duration = 'Spot Nomination for '+@duration EXEC msdb.dbo.sp_send_dbmail @body = @body, @body_format ='HTML', @recipients = 'email@example.com', @subject = @duration DROP TABLE #SpotNomApporval END
The more helpful resource can be found at below links: