john.arnott (9/14/2009)

---

Michael Poppers (9/14/2009)

---

...I didn't know that division by zero was technically OK in T-SQL in certain situations (like when the dividend is undefined). Since it is, one can avoid the query-3 problem with

[font="Courier New"]SELECT NULLIF(@y, 0)/0 --query 3a[/font]

Hmmm.... Divide by zero "OK"??? Not at all....

All you've done is force the return of NULL for the special case of the dividend @y being zero, taking advantage of the behavior that returns NULL for an expression when any part of the expression is NULL. Any other value of dividend @y will still cause a divide by zero error.

Typically, the divide by zero error would occur with a variable divisor, so I suppose you could use this approach to return NULL rather than an error in those cases by wrapping the divisor (not the dividend) in a NullIf function.Declare @Dividend int

Declare @Divisor int

set @Dividend = 15

set @Divisor = 0

Select @Dividend / NullIf(@Divisor,0)

Note, however, that although an empty string will implicitly convert to a zero value int, it will cause an error if the conversion is to numeric (or decimal). So, this NULLIF trick is probably not a great one to depend upon.

DECLARE @x VARCHAR(10), @y VARCHAR(10)

SET @y = ''

SELECT @x/0.0 as Q1 -- query 1

SELECT @x/2.0 as Q2 -- query 2

SELECT @y/2.0 as Q4 -- query 4

SELECT @y/0.0 as Q3 -- query 3

This gives you a conversion error on query 4 (note that I moved it up in the list so you could see that it's not affected by the divide by zero).

-----

edit: added comments about implicit conversion to numeric or decimal