Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

How do you avoid divide by zero error in the below table?

CREATE TABLE [dbo].[TblDivision]
(
    [Numerator] int NOT NULL,
    [Denominator] int NOT NULL,
    [Result] AS (Numerator/ Denominator)
)
GO
Insert into (Numerator, Denominator) TblDivision values (3,0)
GO
share|improve this question
1  
If it is invalid for Denominator to be zero you could add a check constraint on that column. That doesn't answer the question though as it only results in a more meaningful error (closer to the source of the problem) rather than avoiding an error completely. – David Spillett Jan 6 at 12:28
    
Do you intend to use integer division here or not? – Martin Smith Jan 6 at 13:24
    
Have you considered not allow 0 to be a value in that column? Typically what I've seen is a sproc being used for client applications, rather than plain SQL. – Zymus Jan 7 at 7:11

Just add a special case for division by 0:

CREATE TABLE [dbo].[TblDivision]
  (
  [Numerator] int NOT NULL,
  [Denominator] int NOT NULL,
  [Result] AS case when Denominator=0 then 0 else (Numerator/ Denominator) end
  );
share|improve this answer
4  
Or perhaps return NULL rather than 0 if NULLs are not a problem for code reading that column, as the result of divide-by-zero isn't really 0 unless the numerator is 0. – David Spillett Jan 6 at 12:24
3  
Yeah, left as an exercise for the OP, to be honest. Avoiding the error has been answered :) – Phil Jan 6 at 12:25

Similar to @Phil's solution:

CREATE TABLE dbo.TblDivision
(    Numerator int NOT NULL
,    Denominator int NOT NULL
,    Result AS Numerator / nullif(Denominator,0)
);

If Denominator is 0 it is mapped to null via nullif. Since anything divided by null is null the result becomes null in this case.

share|improve this answer
    
Of course this is the cleanest solution. I would accept this one. – Christiaan Westerbeek Jan 6 at 19:41
case 
 when Denominator=0 then 0 
 else Numerator/ Denominator 
end as [Result] 
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.