Equivalent to VB AndAlso in SQL?
Solution 1
The only way to guarantee the order of evaluation is to use CASE
WHERE
CASE
WHEN @a IS NULL THEN 1
WHEN a = @a THEN 1
ELSE 0
END = 1
AND /*repeat*/
In my experience this is usually slower then just letting the DB engine sort it out.
TerrorAustralis's answer is usually the best option for non-nullable columns
Solution 2
Try this:
AND a = ISNULL(@a,a)
This function looks at @a. If it is not null it equates the expression
AND a = @a
If it is null it equates the expression
AND a = a
(Since this is always true, it replaces the @b is null statement)
Solution 3
The query engine will take care of this for you. Your query, as written, is fine. All operators will "short circuit" if they can.
j.strugnell
Updated on June 13, 2022Comments
-
j.strugnell about 2 years
Is there an equivalent to VB's
AndAlso
/OrElse
and C#'s&&
/||
in SQL (SQL Server 2005). I am running a select query similar to the following:SELECT a,b,c,d FROM table1 WHERE (@a IS NULL OR a = @a) AND (@b IS NULL OR b = @b) AND (@c IS NULL OR c = @c) AND (@d IS NULL OR d = @d)
For example, if the "@a" parameter passed in as NULL there is no point in evaluating the 2nd part of the WHERE clause (a = @a). Is there a way to avoid this either by using special syntax or rewriting the query?
Thanks, James.
-
ZygD almost 14 yearsNo they won't. You have no idea what order they will be evaluated in SQL.
-
Eske Rahn over 5 yearsOne should be careful with this approach, depending on the actual query. An IsNull on a field could prevent it from using an index. Here it is on a variable so might work here, but in general be careful. SQL can use index efficiently for a "b is null or b=@b", but not for a "Isnull(b,@b)=@b".
-
Eske Rahn over 5 yearsUnfortunately we only have he CASE not the IF in a WHERE clause. But in a statement block you are right.
-
Eske Rahn over 5 yearsThough slower in general, you might have a complex expression as the second one where the case is faster, and in special cases the second part might not even evaluate (see the example by MikeMuffinMan), so in some cased the andalso/orelse approach with a CASE is a must.