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

I have the below requirements:

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

2. If HourDifferenceCaptured <= 12, no email needs to be sent; nothing needs to be done.

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?

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 = 'intended-recipients@yourorganization.com',  
        @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.