TSQL- How To Loop Through Users From a SQL Server Table And Send Email

Scenario:

We have a table which contains information related to the users, their emails, email subject and email body. We need to write process that will read all the records for which Email is not sent and send them email by using the information available in table. After sending email we will be setting the EmailStatusFlg=1 that means the email is send for this record.

Solution:

To loop through the table records one by one , we will be using TSQL Cursor. By using Cursor we will loop through one record at a time and email and then take next record and so on.

For this example, I have created sample table with some sample data. Initially EmailStatusFlg=0. Once the email will be send , the EmailStatusFlg will be set to 1.

USE TestDB
GO

CREATE TABLE dbo.EmailNotification
  (
     EmailID        INT IDENTITY(1, 1),
     EmailAddress   VARCHAR(100),
     EmailSubject   VARCHAR(200),
     Body           NVARCHAR(MAX),
     EmailStatusFlg BIT
  )

GO

INSERT INTO dbo.EmailNotification

VALUES     ( 'aamirwarraich2001@gmail.com',
             'TestEmail Subject',
             ' This is test email to users',
             0)

GO

INSERT INTO dbo.EmailNotification

VALUES      ( 'aamirwarraich2001@gmail.com',
              'TestEmail Subject 1',
              ' This is test email to users 1',
              0)

Let's write our TSQL Cursor to loop through Email addresses and send email to users one by one.

-->GET the MailProfile by using Below Query
--select * from msdb..sysmail_profile

--> Cursor to Send Email to Recipients one by one

DECLARE @Mail_Profile_Name VARCHAR(100)

SET @Mail_Profile_Name='MailProfileName'

DECLARE @MessageBody NVARCHAR(1000)

DECLARE @RecipientsList NVARCHAR(500)

DECLARE @MailSubject NVARCHAR(500)

DECLARE @EmailID INT
    
DECLARE Email_cursor CURSOR FOR
SELECT Emailid,EmailAddress,EmailSubject,Body 

FROM dbo.EmailNotification WHERE EmailStatusFlg=0
OPEN Email_cursor 
FETCH NEXT FROM Email_cursor  INTO @EmailID,@RecipientsList,@MailSubject,@MessageBody

WHILE @@FETCH_STATUS = 0
  BEGIN
 
  EXEC msdb.dbo.sp_send_dbmail
    @profile_name = @Mail_Profile_Name,
    @recipients = @RecipientsList,
    @body = @MessageBody,
    @subject = @MailSubject;
    
    UPDATE dbo.EmailNotification
    SET EmailStatusFlg=1
    WHERE EmailID=@EmailID
FETCH NEXT FROM Email_cursor  INTO @EmailID,@RecipientsList,@MailSubject,@MessageBody
  END
CLOSE Email_cursor

DEALLOCATE Email_cursor

You can create Stored Procedure or use this script as it. The script can be run manually or you can create SQL Server Agent Job to run on schedule.