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

  • Question

  • 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 requirement:

    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?

    Monday, December 9, 2019 9:00 PM

All replies