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.
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
('[email protected]','Dilbert','What''s up scott?'),
('[email protected]','Late Show','Please read this letter Dave.'),
('[email protected]','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.
Scheduling the stored procedure via a job is up to you.