Wissensdatenbank

Neat IQX way to deal with SQL Divide by Zero errors

Hint Ref: 021408120007
Hint Date: 12/08/2014

Hint Details:

Say you have a function with a calculation which has a divisor which might be zero. In this case, rather than an error, you would like to return the number 1, or perhaps just treat the divisor as being 1, thus returning the dividend, you could use the follow construct:

 

Example 1. - return 1

create MyFunction (in MyNumber int, Divisor int)
 returns int
begin 
 return isnull(MyNumber / nullif(Divisor,0),1);
end

Example 2. - return MyNumber/1

create MyFunction (in MyNumber int, Divisor int)
 returns int
begin 
 return MyNumber / isnull(nullif(Divisor,0),1);
end

Explanation:

If the divisor is zero, nullif() will return NULL making the division sum return NULL, in turn making isnull() return 1


(Please Note: This Procedure can be destructive and should only be used by Advanced Users.  RADical Systems (UK) Limited or its Partners cannot be held responsible, in anyway, for any consequence of using this or any other Database Function, Procedure, XML Script or SQL command.  Responsibility resides solely with the user.  

IT IS HIGHLY RECOMMENDED THAT A FULL AND VALID SPACE MANAGER DATABASE BACKUP IS TAKEN AND VERIFIED AS VALID BEFORE MAKING ANY CHANGES TO THE DATABASE.)