CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Anil
Anil

Reputation: 9

Use nested case, when and else condition in stored procedure

I want to execute a stored procedure with 4 input parameters:

int A, B, C, D

and need to execute as

A/B - C/D

but I have to handle Null and 0 to avoid divide by zero exception.

I was trying this approach, but that didn't work out:

case when D is null or D = 0
       then case when B is null or B=0 then 0
                else cast(A/B) end
case when C is null or C=0
               case when B is null or B= 0, then 0
                else cast( A/B) end
case when A is null or A=0
               case when D is null or D= 0, then 0
                else cast(C/D) end
case when B is null or B=0
               case when D is null or D= 0, then 0
                else cast(C/D) end
else  cast (A/B - C/D) end as Result

Upvotes: 0

Views: 206

Answers (2)

Anil
Anil

Reputation: 9

Here we can use ISNULL function also

CASE WHEN ISNULL(B,0) > 0 THEN A/B ELSE 0 END) - (CASE WHEN ISNULL(D,0) > 0 THEN C/D ELSE 0 END)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270021

Hmmm . . . I am guessing you really want - C/D if A/B is not well-defined.

If so, you don't need a case expression at all:

(coalesce(A / nullif(B, 0), 0) -
 coalesce(C / nullif(D, 0), 0)
) as Result

Upvotes: 1

Related Questions