Send email via SQL Server Agent, based on the result of a T-SQL stored procedure

I have a T-SQL stored procedure (which returns a single scalar value called @HourDifference as an output parameter); you can see the execution below:

DECLARE @HourDifference_output TINYINT; -- I declare a variable to capture the output parameter-- EXEC dbo.sproc_XYZ_Notification @HourDifference = @HourDifference_output OUTPUT; SELECT @HourDifference_output AS HourDifferenceCaptured 

1. If HourDifferenceCaptured > 12 , I will need to send a email
2. If HourDifferenceCaptured

I need to have two schedules, one at 7 AM, the other at 7 PM in the SQL Server Agent.

Can someone provide the code and guide me through this process?

I would question if sending emails via sql server is the best avenue. If you go this route then you will need to configure database mail.

Database Mail is fine; am okay with that; it is a requirement in our organization; the only challenge I have is to take the output parameter as the base for the email.

2 Answers 2

You could create an SQL Server agent job, with a t-sql step which uses msdb.dbo.sp_send_dbmail for sending an e-mail, when required (please see here, for the stored procedure complete reference).

Try something similar to the following:

DECLARE @HourDifference_output TINYINT; EXEC dbo.sproc_XYZ_Notification @HourDifference_output OUTPUT; -- SELECT @HourDifference_output AS HourDifferenceCaptured IF @HourDifference_output > 12 BEGIN EXEC msdb.dbo.sp_send_dbmail @profile_name = 'db_mail_profile_i_have_already_created', @recipients = '[email protected]', @body = 'Too many hours difference.', @subject = 'Automated Message' ; END 

You must have already configured a database mail account, a database mail profile and granted appropriate access to the user running the job step. The second link also contains sample scripts for creating a database mail account and profile, adding the account to the profile and granting access appropriately (I personally prefer to configure database mail via the SSMS db mail wizard).

A decision to be made is whether to create a public or a private profile. You can find more information about the differences here.

Finally, it is, in my opinion, good practice to notify an administrator (via SQL Server Agent build-in mechanisms) when a job / step fails.