SQL: Sending email to an operator rather than a recipient in SQL Server

I was answering a question on a forum the other day, and the person asking the question was puzzled about how to send an email to an operator (i.e. msdb operator) rather than to just any other email address.

The reason he wanted to do that was that he wanted to use the Agent operator system to define a name. He didn’t want to hard-code a recipient name in his code. By having it as an operator, it could be changed later, independent of the code.

Doing that isn’t so hard. You just need to retrieve the operator’s email address from msdb.dbo.sysoperators before sending the mail. Here’s an example:

CREATE PROCEDURE dbo.EmailOperator
@OperatorName sysname,
@EmailBody nvarchar(max),
@EmailSubject nvarchar(255),
@EmailProfileName sysname = NULL -- can use default
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    DECLARE @OperatorEmailAddress nvarchar(256) 
        = (SELECT so.email_address 
           FROM msdb.dbo.sysoperators AS so
           WHERE so.[name] = @OperatorName);

    IF @OperatorEmailAddress IS NOT NULL
    BEGIN
        EXEC msdb.dbo.sp_send_dbmail  
            @profile_name = @EmailProfileName,  
            @recipients = @OperatorEmailAddress,  
            @body = @EmailBody,  
            @subject = @EmailSubject ;
    END;
END;
GO

And then send the email like this:

EXEC dbo.EmailOperator 
    @OperatorName = N'OnCallDBAs',
    @EmailBody = 'It''s nearly time to plan the roster for Christmas again',
    @EmailSubject = 'Important message for all DBAs';
GO

And as BWoodburn noted in the comments, there is also a built-in function that you might be able to call:

sp_notify_operator

It offers slightly different options.

I hope that one of these helps someone.

2019-11-14