# Avoiding a divide by zero error in a computed column

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

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