Equivalent to VB AndAlso in SQL?

Question

Is there an equivalent to VB AndAlso / OrElse and C # && / || in SQL (SQL Server 2005). I run 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 parameter "@a" is passed as NULL, it makes no sense to evaluate the second part of the WHERE clause (a = @a). Is there a way to avoid this using special syntax or rewriting a query?

Thanks, James.

Answer 1

The only way to guarantee the evaluation order 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 than just letting the DB machine figure it out.

The TerrorAustralis answer is generally the best option for non-nullable columns.

Answer 2

Try the following:

 AND a = ISNULL(@a,a) 

This function looks at @a. If it is not equal to zero, it is equal to the expression

 AND a = @a 

If it is zero, it is equal to the expression

 AND a = a 

(Since this is always true, it replaces the @b operator with an empty one)

Answer 3

The query engine will take care of this for you. Your request, as it is written, is in order. All operators will be short-circuited if they can.

Answer 4

Another way:

 IF (@a > 0) IF (@a = 5) BEGIN END 

Other, if after the condition the logic "AndAlso" is executed.

I want to emphasize that this is just a short way to write:

 IF (@a > 0) IF (@a = 5) BEGIN END 

Answer 5

Take this example:

 SELECT * FROM Orders WHERE orderId LIKE '%[0-9]%' AND dbo.JobIsPending(OrderId) = 1 

Orders.OrderId is varchar (25)

dbo.JobIsPending(OrderId) UDF with int parameter

No short circuit occurs because the conversion fails in dbo.JobIsPending(OrderId) when Orders.OrderId NOT LIKE '%[0-9]%'

tested on SQL Server 2008 R2