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
@EmailProfileName sysname = NULL -- can use default
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
@profile_name = @EmailProfileName,
@recipients = @OperatorEmailAddress,
@body = @EmailBody,
@subject = @EmailSubject ;
And then send the email like this:
@OperatorName = N'OnCallDBAs',
@EmailBody = 'It''s nearly time to plan the roster for Christmas again',
@EmailSubject = 'Important message for all DBAs';
I hope that helps someone.