HiveBrain v1.2.0
Get Started
← Back to all entries
debugsqlModerate

Avoiding a divide by zero error in a computed column

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
dividecomputederrorcolumnzeroavoiding

Problem

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

Solution

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
  );

Code Snippets

CREATE TABLE [dbo].[TblDivision]
  (
  [Numerator] int NOT NULL,
  [Denominator] int NOT NULL,
  [Result] AS case when Denominator=0 then 0 else (Numerator/ Denominator) end
  );

Context

StackExchange Database Administrators Q#125363, answer score: 11

Revisions (0)

No revisions yet.