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
Post a Comment