CODEWITHSUNDEEP
sql-serverexpressionconditional-statementsshort-circuiting
CRice
CRice

Reputation: 12567

Do all parts of a SQL SERVER expression using 'OR' get evaluated?

Given:

WHERE (@Id Is NULL OR @Id = Table.Id)

If @Id is null: the expression evaluates to true. Does the second part @Id = Table.Id still get considered? or is it sufficient that the expression evaluates to true given that the first part is (which is the case in c#).

This is relevant because of some much more complex OR statements where it is important to know if all parts are getting evaluated.

UPDATE:

I have since found this syntax to be a good alternative

WHERE (Table.Id = ISNULL(@Id, Table.Id))

Upvotes: 8

Views: 2705

Answers (2)

Remus Rusanu
Remus Rusanu

Reputation: 294317

Sometimes they are, sometimes they aren't. SQL Server does not guarantee boolean operator short circuit, don't rely on it for correctness. See this blog entry: Boolean Operator Short Circuit.

Complex queries that depend on @variables like this are much better written as explicit IF statements:

IF (@id IS NULL)
  SELECT ... FROM ... WHERE ...
ELSE
  SELECT ... FROM ... WHERE ...

Upvotes: 11

JonH
JonH

Reputation: 33153

Execution plans may not be so great with a query like that. Both will be evaluated.

Upvotes: 1

Related Questions