0

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user3812887
  • 439
  • 12
  • 33
  • 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. – Ross Bush Dec 09 '19 at 20:59
  • 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. – user3812887 Dec 09 '19 at 21:04

2 Answers2

0
USE <database_name>
GO
DECLARE   @subject  NVARCHAR(max) = 'Email subject'
        , @body     NVARCHAR(MAX)

DECLARE @HourDifference_output TINYINT;
EXEC dbo.sproc_XYZ_Notification @HourDifference = @HourDifference_output OUTPUT;
SELECT @HourDifference_output AS HourDifferenceCaptured

IF (@HourDifference_output> 12) 
BEGIN

SET @body = '<!DOCTYPE html>
                        <html>
                        <body>
                        <body style="color:black;  font-family:Times New Roman; font-size:14x"> .......... body text ......... </body>
                        <body style="color:SlateGray; font-family:Times New Roman; font-size:14px;line-height: 1;">  ------------------------------------------------------------------' + ' 
                        <body style="color:SlateGray; font-family:Times New Roman; font-size:14px;line-height: 1;">  ------------------------------------------------------------------' + '                                                             
                        </body>
             </HTML>'

    EXEC msdb.dbo.sp_send_dbmail   @profile_name = 'Profile name'
                                ,  @recipients = 'distibution email'
                                ,  @body = @body
                                ,  @body_format = 'HTML'
                                ,  @subject = @subject
END
GO
Israel H.
  • 54
  • 4
0

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.

ClmCpt
  • 421
  • 2
  • 8