Michael Poppers (9/14/2009)
Thanks for the question! 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