SET ARITHABORT OFF;
SET ANSI WARNINGS OFF;
Sometimes when I am developing a large Select command I don’t want to take the time and “correctly” setup every calculation.
For example:
Select (long calculation for the dividend)/(different long calculation for the divisor) from table where...
The correct way to do this would be to write this with NULLIF.
Select (long calculation for the dividend)/NULLIF((different long calculation for the divisor),0) from table where...
Sometimes I don’t really want to add more parentheses to the query while I’m actively adding and changing different parts of the query. I want to keep everything as clean as possible until I finalize the statement.
The easiest way I found to handle this is to change the SQL settings ARITHABORT and ANSI WARNINGS to OFF before running the query.
First you might want to find out if they are off or on so you can reset them later.
select SESSIONPROPERTY('ARITHABORT');
select SESSIONPROPERTY('ANSI WARNINGS');
Then change the settings and run your query.
SET ARITHABORT OFF;
SET ANSI WARNINGS OFF;
Select (long calculation for the dividend)/(different long calculation for the divisor) from table where...
ARITHABORT is the option that terminates a query when there is overflow or divide by zero.
ANSI WARNINGS rolls back queries and gives an error message when there is overflow or divide by zero. They serve similar purposes.
Any calculation that divides by zero will return NULL for that value. 4/0 will return NULL.
It will NOT abort the query.
After I finish testing my query I change the settings back to ON. This is a quick, dirty fix that WILL result in slower performance rather than writing it with NULLIF functions.