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