SQL email notification

user_5883618 0
Viewed 51

For your kind help please as per below procedure it send me email with loans which will end after 2 months from current date, and if there is no results it still send me email says no affected rows

All what I need just receive email when there is result and if there is no result don't send email

ALTER PROCEDURE [LoanEndByTwoMonths_SP] 
-- Add the parameters for the stored procedure here
AS

BEGIN
    SET NOCOUNT ON;
    IF
    (
        SELECT COUNT(LoanID)
        FROM ELSBassel.Loan.Loans
    ) > 0

        BEGIN
            EXEC msdb.dbo.sp_send_dbmail 
                 @profile_name = 'ELS', 
                 @recipients = 'Bassel.Mahmoud@test.com', 
                 @query = 'SELECT LoanStartDate, 
           LoanStartDate + DATEADD(MONTH, AvailabilityPMonth, 0) - 1 AS LoanEndDate, 
           [FinanceSourceID]
    FROM ELSBassel.Loan.Loans 
    WHERE(LoanStartDate + DATEADD(MONTH, AvailabilityPMonth, 0) - 1) = (DATEADD(MONTH, 2, GETDATE()))
    ORDER BY LoanStartDate + DATEADD(MONTH, AvailabilityPMonth, 0) - 1 DESC;', 
                 @execute_query_database = 'ELSBassel', 
                 @subject = 'Loan will end after two months';
            COMMIT;
    END;
END;
GO
sql-server
1 Answers

I think that just adding the same WHERE condition that you have in the WHERE clause should fix your issue:

ALTER PROCEDURE [LoanEndByTwoMonths_SP] 
-- Add the parameters for the stored procedure here
AS

BEGIN
    SET NOCOUNT ON;
IF
(
    SELECT COUNT(LoanID)
    FROM ELSBassel.Loan.Loans
    WHERE (LoanStartDate + DATEADD(MONTH, AvailabilityPMonth, 0) - 1) 
        = (DATEADD(MONTH, 2, GETDATE()))
) > 0

    BEGIN
        EXEC msdb.dbo.sp_send_dbmail 
             @profile_name = 'ELS', 
             @recipients = 'Bassel.Mahmoud@test.com', 
             @query = 'SELECT LoanStartDate, 
       LoanStartDate + DATEADD(MONTH, AvailabilityPMonth, 0) - 1 AS LoanEndDate, 
       [FinanceSourceID]
FROM ELSBassel.Loan.Loans 
WHERE(LoanStartDate + DATEADD(MONTH, AvailabilityPMonth, 0) - 1) = (DATEADD(MONTH, 2, GETDATE()))
ORDER BY LoanStartDate + DATEADD(MONTH, AvailabilityPMonth, 0) - 1 DESC;', 
             @execute_query_database = 'ELSBassel', 
             @subject = 'Loan will end after two months';
        COMMIT;
END;
END;
GO
user_108145030
Related