none
Avoiding (Divided by zero) Error RRS feed

  • Question

  • Lets say we have a table tblFormula whose design structure is

    ID        (Auto Incremented)
    Num1   (int)
    Num2   (int)
    Num3   (Computed Column) Num1/Num2

    Now, Kindly concentrate on Num3 Column, it is a computed column, It divides Num1 by Num2, but If we have the value of zero in Num2, then it throws the following error when we load the table from SQL server management studio.

    I want that, if Num1 or Num2 contains a zero value then the Num3 should display a 0 instead of displaying the "divided by zero error". Can someone help me how can we do that ?

    Saturday, February 6, 2016 3:11 PM

Answers

  • isnull(num1/nullif(num2,0),0)
    
    
    create table test(num1 int, num2 int, num3 as isnull(num1/nullif(num2,0),0) )
    insert into test values(1,2),(2,0)
    
    select * from test
    
    drop table test

    • Marked as answer by Sid Williams Saturday, February 6, 2016 4:13 PM
    Saturday, February 6, 2016 3:16 PM
    Moderator

All replies

  • isnull(num1/nullif(num2,0),0)
    
    
    create table test(num1 int, num2 int, num3 as isnull(num1/nullif(num2,0),0) )
    insert into test values(1,2),(2,0)
    
    select * from test
    
    drop table test

    • Marked as answer by Sid Williams Saturday, February 6, 2016 4:13 PM
    Saturday, February 6, 2016 3:16 PM
    Moderator
  • Thank you so much.
    Saturday, February 6, 2016 4:14 PM