Creating stored procedure in sql using the given date and emails

I am setting up job schedule for sending notifications using the email addresses present in my table where their active flag is set to true. This notification will be sent every 24 hours.

In my table, I have this:

Email ID    Request ID     Receiver             Date Created  Email Flag
 1            2           receiver1@domain.com   9/10/2015        1
 2            4           receiver2@domain.com   9/11/2015        1   
 3            5           receiver3@domain.com   9/15/2015        1

This time, the email should be sent 24 hours from the date it was created and the email flag should be true.
I would like to ask for the basic algorithm that would serve as my guide to make the stored procedure for this one?

I set up my first code:

I just typed this in plain text so I don’t know if some syntax is wrong.

USE [databasename]
GO


Declare 

@recipients AS VARCHAR(100)
@subject AS VARCHAR(200)
@body AS VARCHAR(200)
@s_date AS DateTime
@flag

select email_isactive from dbo.emails AS @flag where dbo.email_isactive == 1

if(flag == 1)

BEGIN

  EXEC msdb.dbo.sp_send_dbmail

   @recipients = 'Select email_receiver from dbo.rms_emails where dbo.is_active ==1'
   @subject = "Referral Monitoring Notification: For Approval"
   @body = "This serves as a notification that there's a regularization entry pending for your approval.
    Kindly visit http:// for more details. Thanks."

I haven’t finished my code. I was stuck in the recipients portion since there are a lot of emails that will be used in the recipients. And also I have to consider the date if where should I put it and check if the current date is in 24 hours already from the date it was created.

I really need your assistance. I’m a little bit confused.

Thanks for your help!


Source: mysql

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.