8

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
  • 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 '16 at 12:28
  • Do you intend to use integer division here or not? – Martin Smith Jan 6 '16 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 '16 at 7:11
12

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
  );
  • 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 '16 at 12:24
  • 3
    Yeah, left as an exercise for the OP, to be honest. Avoiding the error has been answered :) – Philᵀᴹ Jan 6 '16 at 12:25
10

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.

  • Of course this is the cleanest solution. I would accept this one. – Christiaan Westerbeek Jan 6 '16 at 19:41
-2
case 
 when Denominator=0 then 0 
 else Numerator/ Denominator 
end as [Result] 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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