Saturday, October 31, 2015

SQL Server Error Msg: Divide by zero error encountered.

I was reviewing my posts today and recognized that I don't have near enough SQL Server error messages w/resolutions up here.  My bad.  I am going to begin building a reference for some of the more common (and more frustrating) errors that I'm sure you've all seen.  

First up is the Divide by Zero error.  This error is caused by performing a division where the denominator is a zero.  Here's a very quick example:

    -- sample data in @numbers 
    DECLARE @numbers TABLE (Numerator INT, Denominator INT)
    INSERT @numbers (Numerator,Denominator)
    VALUES (100,10),
               (20,4),
             (36,9),
             (22,0);  -- note the 0 denominator

    -- perform your division
    SELECT 
         Numerator / Denominator [Result]
    FROM
        @numbers

You'll receive this error:

    (4 row(s) affected)
    Msg 8134, Level 16, State 1, Line 8
    Divide by zero error encountered.

We can workaround the problem using this CASE statement.

    -- workaround CASE
    SELECT
        CASE WHEN Denominator = 0 THEN 0 ELSE Numerator / Denominator END [Result]
    FROM
        @numbers

No more error.  This is your result, where that 0 is now returned without the error:

    Result
      10
      5
      4
      0

We can also use NULLIF for the workaround, rather than the CASE statement, like this:

    -- workaround NULLIF
    SELECT 
         Numerator / NULLIF(Denominator,0) [Result]
    FROM 
          @numbers

Slightly different return:

    Result
      10
      5
      4
      NULL

NULLIF returns a NULL value if the two specified expressions are equal. More details here:     https://msdn.microsoft.com/en-us/library/ms177562.aspx

I'll be back with more SQL Server Error messages soon.  Until then, feel free to post any errors you're struggling with, and I'll help you out.

No comments:

Post a Comment