Divide by Zero Error on Percentage Calculation 1

[bustercoder]

Hello,
I have this percentage calc for fields in my stored proc:

[DISCOUNT %] = CAST(ROUND (SOP10100.TRDISAMT / SOP10200. XTNDPRCE * 100.0,2) AS NUMERIC (19, 2 )),

[DISC WARNING] = CASE WHEN CAST(ROUND(SOP10100 .TRDISAMT / SOP10200.XTNDPRCE * 100.0,2) AS NUMERIC (19, 2)) <> 50.00 THEN CAST(ROUND(SOP10100 .TRDISAMT / SOP10200.XTNDPRCE * 100.0,2) AS NUMERIC (19, 2)) ELSE null END

I know that I am getting the dreaded 'Divide by Zero Error' because because in some cases the SOP10100.TRDISAMT will be smaller OR larger than the SOP10200.XTNDPRCE. Can someone please show me how to handle this condition to avoid this 'Divide by Zero Error? I've researched it but I can't see how I should handle this? In other words, should I handle it through logic flow (IF/THEN) or write the calculation all together differently?

Thanks,
Buster

 

[SQLDenis]

use case

example

case SOP10200.XTNDPRCE when 0 then 0 else CAST(ROUND (SOP10100.TRDISAMT / SOP10200. XTNDPRCE * 100.0,2) AS NUMERIC (19, 2 )) end as [DISCOUNT %],

etc etc etc


Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions