Most of the times there are requirements when we need to send automatic notification emails to all or specific email address in the database based on some condition.

For example send birthday wishes to all customers whose birthday matches the current day.  Connect to the database engine of SQL server using SQL Server Management Studio.  Expand the SQL Server Agent. You will see a Jobs folder over there. Right click on jobs and choose NewJob as shown below screen.

 A New Job popup will appear. Specify the name of the job and description as shown below.

1.     Name - Name of the Job

2.     Description – Description of the Job (Optional)

3.     Enabled – Determines whether job is enabled or disabled

 

Select "Steps" option in the left menu of the New Job popup window. A SQL job can contain one or more steps. A step might be simply an SQL statement or a stored procedure call. Add you step here 

In the New Job Step Window enter the following details

1.     Step Name - Name of the Step

2.     Type  – Select Transact SQL Script

3.     Database – Select the database on which you want to run the script.

4.     Command – Paste the SQL Script which you wish the Job Scheduler to run 

And then click on OK button it will show New Job window.

 

Select "Schedules" option in the left menu of the New Job popup window.  In the New Schedule Window enter the following details

1.     Name - Name of the Step

2.     Enabled – Determines whether Schedule is enabled or disabled

3.     Schedule Type – Select Recurring schedule type since we need to run it daily

4.     Occurs – Daily since we need to run it daily

5.     Daily Frequency – Since we need to run once a day, select the time you wish to run

6.     Start date – Select date from when you want the schedule to run.

 Select OK button a new job is successfully created.  

You will see a new entry in the SQL Server agent -----> Jobs

To start the job right click the job and in the context menu click Start Job as shown bellow.

Stored Procedure Script:

Note: 

1.  Add database name and schema to the table.

2.  Add database name and schema to the sending email  

            (EXEC msdb.dbo.sp_send_dbmail
            @profile_name = 'EmailSending',
            @recipients = @EmpMaild,
            @copy_recipients = @MailID,
            @body=@body,
            @subject = 'Scheduled Request Will be exprie')

DECLARE
@out_desc VARCHAR(1000),
@out_mesg VARCHAR(10)

DECLARE @name VARCHAR(20),
@birthdate datetime,
@email NVARCHAR(50)

DECLARE @body NVARCHAR(1000)

DECLARE C1 CURSOR READ_ONLY
FOR
SELECT [name], [birthdate], [email]
FROM Customers
OPEN C1
FETCH NEXT FROM C1 INTO
@name, @birthdate, @email
WHILE @@FETCH_STATUS = 0
BEGIN
      IF DATEPART(DAY,@birthdate) = DATEPART(DAY,GETDATE())
      AND DATEPART(MONTH,@birthdate) = DATEPART(MONTH,GETDATE())
      BEGIN
            SET @body = '<b>Happy Birthday ' + @name +
            '</b><br />Many happy returns of the day'
            + '<br /><br />Customer Relationship Department'
            EXEC sp_send_mail  'sender@abc.com',
            'xxxxxxx',
            @email,
            'Birthday Wishes',
            @body,
            'htmlbody',
            @output_mesg = @out_mesg output,
            @output_desc = @out_desc output
            PRINT @out_mesg
            PRINT @out_desc
      END
      FETCH NEXT FROM C1 INTO
      @name, @birthdate, @email
END
CLOSE C1
DEALLOCATE C1