Procedures to send email with multiple data

[FooKelvin]

Hi,

I have a Procedures ready to auto send email to employee when the reminder date equal today.

Let me show you what is inside my employee database.

 

 

As you can see, there is two records for employee 1001.

This is because this employee short notice for 2 months. [sdate]

There is also a shortNotice which is in December is 4 day, in January is 18 days.

So, the consist days is 18+4=22 Days

 

This is my procedure code, it's working, this procedure will trigger out email if meet the criteria.

***REMEMBER TO CHANGE THE SERVER NAME & PORT NUMBER***

USE [RSA]
GO
/****** Object:  StoredProcedure [dbo].[sp_send_cdosysmail]    Script Date: 12/10/2015 3:55:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_send_cdosysmail] 
@From varchar(100)="notification@mail.com",
@Subject varchar(100)="A Reminder for you",
@body varchar(4000)=" ",
@attachments varchar(4000)=NULL


AS
DECLARE
@out_desc VARCHAR(1000),
@out_mesg VARCHAR(10)
 
DECLARE 
@empid nvarchar(255),
@Name nvarchar(255),
@Email nvarchar(255),
@reminderDate datetime,
@lastDay datetime,
@shortNotice int,
@consistsDays int,
@sdate datetime
 
Declare C1 CURSOR READ_ONLY
FOR
SELECT DISTINCT [empid],[Name],[Email],[reminderDate],[lastDay],[shortNotice],[consistsDays],[sdate]
FROM [resigntblView]

Declare @iMsg int
Declare @hr int
Declare @source varchar(255)
Declare @description varchar(500)
Declare @output varchar(1000)
Declare @files table(fileid int identity(1,1),[file] varchar(255))


OPEN C1
FETCH NEXT FROM C1 INTO
@empid,@Name, @Email, @reminderDate, @lastDay,@shortNotice,@consistsDays,@sdate
WHILE @@FETCH_STATUS = 0
BEGIN

IF DATEPART(DAY,@reminderDate) = DATEPART(DAY,GETDATE())
      AND DATEPART(MONTH,@reminderDate) = DATEPART(MONTH,GETDATE())
		 AND DATEPART(YEAR,@reminderDate) = DATEPART(YEAR,GETDATE())
      BEGIN
	  SET @body = '<b>Hi ' + @Name +
            '</b><br />This is a reminder'
            + '<br /><br />Thank You'
--************* Create the CDO.Message Object ************************ 
EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT 
--***************Configuring the Message Object ****************** 
-- This is to configure a remote SMTP server. 
-- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp 
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields ("http://schemas.microsoft.com/cdo/configuration/sendusing"). Value','2' 
-- This is to configure the Server Name or IP address. 
-- Replace MailServerName by the name or IP of your SMTP Server. 
EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields ("http://schemas.microsoft.com/cdo/configuration/smtpserver"). Value', '#######' --PUT YOUR SERVER HERE
-- Save the configurations to the message object. 
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null 
-- Set the e-mail parameters. 
EXEC @hr = sp_OASetProperty @iMsg, 'To', @Email 
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From 
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject 
-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'. 
EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBody', @body
EXEC @hr = sp_OAMethod @iMsg, 'Send' 
-- Do some error handling after each step if you need to. 
-- Clean up the objects created. 
EXEC @hr = sp_OADestroy @iMsg 
END
      FETCH NEXT FROM C1 INTO
      @empid,@Name, @Email, @reminderDate,@lastDay,@shortNotice,@consistsDays,@sdate
	  END
CLOSE C1
DEALLOCATE C1

Once the Procedures  is execute, this is the OUTPUT that i received in my inbox:

Hi Foo, Kelvin
This is a reminder

Thank You

This Email will Trigger two times since there is a two column of data. My question is, how to merge some of the data in one, and send the email only once.

 

Expected Output:

Hi Foo, Kelvin,

You are required to compensate USD 100 to the Company in view of your 22 days of short notice.
The 1 month of your notice period consist of 22 working days, (01-12-2015 - 31-12-2015), 4 Days
The 2 month of your notice period consist of 22 working days, (01-01-2016 - 31-01-2016), 18 Days


Thank You. 

Thanks for your big help.

Edited December 11, 2015 by FooKelvin

[requinix]

Why the heck are you sending emails from within the database server?

[FooKelvin]

Why the heck are you sending emails from within the database server?

 

This is because i'm using SQL Agent to help me schedule the email trigger. I need the email to be send everyday automatically.