Menu
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))
782
SQL Server query operators OR and AND are commutative. There is no inherent order and the query optimizer is free to choose the path of least cost to begin evaluation. Once the plan is set, the other part is not evaluated if a result is pre-determined.
A CASE expression evaluates to the first true condition. If there is no true condition, it evaluates to the ELSE part. If there is no true condition and no ELSE part, it evaluates to NULL .
Within an expression, higher precedence operators will be evaluated first. The precedence of operators goes as follows: =, <, >, <=, >=, <>, != , ~=, ^=, IS NULL, LIKE, BETWEEN, IN.
The SQL Server OR is a logical operator that allows you to combine two Boolean expressions. It returns TRUE when either of the conditions evaluates to TRUE . In this syntax, the boolean_expression is any valid Boolean expression that evaluates to true, false, and unknown.
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 ...
Execution plans may not be so great with a query like that. Both will be evaluated.
35
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
