mercredi 24 août 2016

Using an IF statement in SQL to determine whether an email is sent

I currently have a stored procedure (Utils.DailySalesTaxDue) that calculates the sum of a field (SalesTax). Currently the the procedure will determine the amount and then execute another sproc (Utils.SendEmail).

There are times when the sum of SalesTax equals $0.00. In these cases, I do not want to have an email sent out. I assume an IF statement is needed. I have an if statement that is using a wildcard statement to check for %$0.00% within the @ReportBody variable. It works, however I know this is probably not the best way to do this. This is what I currently have.

declare @ReportHTML varchar(max), 
            @Recipient varchar(255), 
            @EmailSubject varchar(255), 
            @ReportBody varchar(max)
select 
        @ReportBody = 'Sales Tax Total due is: <b>' + format(isnull(sum(SalesTax),0.00),'C','en-us') + '</b>'
    from 
        Final.FactTransactionDay f
            inner join Final.SaleOutcome so
                on  f.SaleOutcomeKey = so.SaleOutcomekey
    where
        f.StoreID = @StoreID and 
        f.=TransactionDateKey = @TransactionDateKey and
        so.NetCount <> 0

/////Additional Code

--Do not send the email if claim amount is $0.00
    if @ReportBody not like '%$0.00%'
    begin
        --Send email
        exec Utils.SendEmail
                @HTML = @ReportHtml,
                @Recipients = @Recipient,
                @From = 'NoReply@SalesSupport.net',
                @FromName = 'Sales',
                @Subject = @EmailSubject
    end

What would be a better approach to checking whether sum(SalesTax) is not equal to $0.00, and if it is not equal to then fire off the Utils.SendEmail stored procedure?

Aucun commentaire:

Enregistrer un commentaire