Deal with divided by 0 : where « Query « SQL Server / T-SQL Tutorial

3>
4> CREATE TABLE MyTable(
5> col1 int NOT NULL,
6> col2 int NOT NULL)
7> INSERT INTO MyTable VALUES(1, 1)
8> INSERT INTO MyTable VALUES(1, 0)
9> INSERT INTO MyTable VALUES(-1, 1)
10>
11>
12> SELECT * FROM MyTable WHERE (col1 / col2 > 0)
13> GO

(1 rows affected)

(1 rows affected)

(1 rows affected)
col1        col2
----------- -----------
Msg 8134, Level 16, State 1, Server BCE67B1242DE45A\SQLEXPRESS, Line 12
Divide by zero error encountered.
          1           1
Msg 8134, Level 16, State 1, Server BCE67B1242DE45A\SQLEXPRESS, Line 12
Divide by zero error encountered.
1> SELECT * FROM MyTable WHERE (col2 <> 0) AND (col1 / col2 > 0)
2> GO
col1        col2
----------- -----------
          1           1

(1 rows affected)
1> SELECT * FROM MyTable WHERE (col1 / col2 > 0) AND (col2 <> 0)
2> GO
col1        col2
----------- -----------
          1           1

(1 rows affected)
1> SELECT * FROM MyTable WHERE
2>   CASE
3>     WHEN col2 = 0        THEN 0
4>     WHEN col1 / col2 > 0 THEN 1
5>     ELSE 0
6>   END = 1
7> GO
col1        col2
----------- -----------
          1           1

(1 rows affected)
1>
2> drop table MyTable
3> GO

1.2.where
	1.2.1.	All the operators we can use with the WHERE clause:
	1.2.2.	Calculation in where clause
	1.2.3.	Where with subquery
	1.2.4.	A SELECT statement that uses a subquery in the WHERE clause: greater than average
	1.2.5.	where in a subquery
	1.2.6.	The syntax of a WHERE clause that uses an IN phrase with a subquery
	1.2.7.	The syntax of a WHERE clause that compares an expression with the value returned by a subquery
	1.2.8.	WHERE clause can contain other comparison operators, including the following:
	1.2.9.	An expression can also be a part of the condition in the WHERE clause
	1.2.10.	Deal with divided by 0