views:

342

answers:

3

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.

+3  A: 

The query engine will take care of this for you. Your query, as written, is fine. All operators will "short circuit" if they can.

TheSoftwareJedi
No they won't. You have no idea what order they will be evaluated in SQL.
gbn
+1  A: 

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)

TerrorAustralis
A: 

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 0
      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

gbn