To notify an operator of job status through Transact-SQL.
In Object Explorer, connect to an instance of Database Engine.
On the Standard bar, click New Query.
-- adds an e-mail notification for the specified alert (Test Alert).
-- This example assumes that Test Alert already exists
-- and that Francois Ajenstat is a valid operator name.
USE msdb ;
GO
EXEC dbo.sp_add_notification
@alert_name = N'Test Alert',
@operator_name = N'Francois Ajenstat',
@notification_method = 1 ;
GO
References:
https://docs.microsoft.com/en-us/sql/ssms/agent/notify-an-operator-of-job-status?view=sql-server-2017