sqlsql-serveremailsp-send-dbmaildbmail

Send email from SQL server for each row of a dataset


I have built a stored procedure for sending an email reminder to a set of employees each week. The SQL server agent runs a scheduled procedure each week that builds this weeks dataset of employees and then I need to have each of the employees receive an email but I cannot use the email stored proc inline with the scheduled SELECT statement.

This would be possible using a function but I am using EXEC msdb.dbo.sp_send_dbmail to send mail which cannot be executed in a function.


Solution

  • Use a stored procedure. Inside the stored procedure use a cursor to get the details of the email and what you want to send. Call the sp_send_dbmail repeatedly until all employees get an email.

    Okay, it took me a few minutes to set you up a test case database. We are sending emails to Scott Adams, Dave Letterman and Bill Gates.

    /*  
        Setup test database.
    */
    
    -- Use master
    USE [master]
    GO
    
    -- Create a simple database using models attributes
    CREATE DATABASE [MAIL];
    GO
    
    -- Use mail
    USE [MAIL]
    GO
    
    -- Drop existing
    IF OBJECT_ID(N'[DBO].[EMAIL_LIST]') > 0
    DROP TABLE [DBO].[EMAIL_LIST]
    GO
    
    -- Create new
    CREATE TABLE [DBO].[EMAIL_LIST]
    (
        ID INT IDENTITY(1,1) PRIMARY KEY,
        EMAIL_ADDRESS VARCHAR(64),
        EMAIL_SUBJ VARCHAR(64),
        EMAIL_BODY VARCHAR(256),
        SENT_FLAG TINYINT DEFAULT (0)
    );
    
    -- Insert simple data
    INSERT INTO [DBO].[EMAIL_LIST] (EMAIL_ADDRESS, EMAIL_SUBJ, EMAIL_BODY)
    VALUES
    ('scottadams@aol.com','Dilbert','What''s up scott?'),
    ('lateshow@pipeline.com','Late Show','Please read this letter Dave.'),
    ('billg@microsoft.com','Gates','How''s the weather in Seatle?');
    
    -- Show the data
    SELECT * FROM [DBO].[EMAIL_LIST];
    

    This stored procedure reads the email list of unsent emails and sends out the emails.

    /*  
        Create stored procedure
    */
    
    -- Drop existing
    IF OBJECT_ID(N'[DBO].[SEND_EMAILS]') > 0
    DROP PROCEDURE [DBO].[SEND_EMAILS]
    GO
     
    -- Create new
    CREATE PROCEDURE [dbo].[SEND_EMAILS]
    AS
    BEGIN
    
        -- Error handling variables
        DECLARE @err_number int;
        DECLARE @err_line int;
        DECLARE @err_message varchar(2048);
        DECLARE @err_procedure varchar(2048);
    
        -- ** Error Handling - Start Try **
        BEGIN TRY
    
        -- No counting of rows
        SET NOCOUNT ON;
        
        -- Declare variables
        DECLARE @VAR_ADDRESS VARCHAR(64);
        DECLARE @VAR_SUBJ VARCHAR(64);
        DECLARE @VAR_BODY varchar(256);
    
        -- Get email list
        DECLARE VAR_CURSOR CURSOR FOR
            SELECT EMAIL_ADDRESS, EMAIL_SUBJ, EMAIL_BODY
            FROM [DBO].[EMAIL_LIST] 
            WHERE SENT_FLAG = 0;
            
        -- Open cursor
        OPEN VAR_CURSOR;
    
        -- Get first row
        FETCH NEXT FROM VAR_CURSOR 
            INTO @VAR_ADDRESS, @VAR_SUBJ, @VAR_BODY;
    
        -- While there is data
        WHILE (@@fetch_status = 0)
        BEGIN
            -- Send the email
            EXEC msdb.dbo.sp_send_dbmail 
                @recipients = @VAR_ADDRESS,
                @subject = @VAR_SUBJ,
                @body = @VAR_BODY,
                @body_format = 'HTML' ;  
    
            -- Grab the next record
            FETCH NEXT FROM VAR_CURSOR 
                INTO @VAR_ADDRESS, @VAR_SUBJ, @VAR_BODY;
        END
    
        -- Close cursor
        CLOSE VAR_CURSOR;
    
        -- Release memory
        DEALLOCATE VAR_CURSOR;            
    
        -- Update the table as processed
        UPDATE [DBO].[EMAIL_LIST] 
        SET SENT_FLAG = 1
        WHERE SENT_FLAG = 0;
    
        -- ** Error Handling - End Try **
        END TRY
    
        -- ** Error Handling - Begin Catch **
        BEGIN CATCH
           
          -- Grab variables 
          SELECT 
              @err_number = ERROR_NUMBER(), 
              @err_procedure = ERROR_PROCEDURE(),
              @err_line = ERROR_LINE(), 
              @err_message = ERROR_MESSAGE();
    
          -- Raise error
          RAISERROR ('An error occurred within a user transaction. 
                      Error Number        : %d
                      Error Message       : %s  
                      Affected Procedure  : %s
                      Affected Line Number: %d'
                      , 16, 1
                      , @err_number, @err_message, @err_procedure, @err_line);       
    
        -- ** Error Handling - End Catch **    
        END CATCH                
                
    END
    

    Looking at the MSDB.[dbo].[sysmail_mailitems] table, we can see the items were queued to go. This depends upon database mail being set up with a public default profile.

    enter image description here

    Scheduling the stored procedure via a job is up to you.