SQL Server Database Mail: Make sure Database Mail Procedures, Profiles, and Operators Exist

If you'd like to send an email from SQL Server, you can do it using database mail, presuming that database mail is installed.

If you'd like to send a notification to an operator from a stored procedure or any other T-SQL code, you should use the procedure sp_notify_operators.

But what happens if database mail isn't installed? You will get an error that the procedure doesn't exist. You could try to catch that error with a BEGIN TRY...BEGIN CATCH block, but you could also test to see if the procedure exists.

 
 IF EXISTS
 (
  SELECT
       *
  FROM
       msdb.sys.objects
  WHERE
       type = 'P'
       AND name = 'sp_notify_operator' 
 )
 BEGIN

  -- do something here, such as calling the procedure

 END

You could also check to see if the email profile or the operator exists.

 -- Make sure the profile exists
 IF NOT EXISTS
 ( 
  SELECT
      *
  FROM
   msdb.dbo.sysmail_profile
  WHERE
   name = @email_profile_name
 )
 BEGIN
 
  RAISERROR( 'That database mail profile does not exist.', 16, 1 );
  RETURN;

 END

 -- Make sure the operator exists
 IF NOT EXISTS
 ( 
  SELECT
      *
  FROM
      msdb.dbo.sysoperators
  WHERE
   NAME = @operator_name 
 )
 BEGIN
 
  RAISERROR( 'That operator name does not exist.', 16, 1 );
  RETURN; 

 END