CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sqlshort-circuiting
j.strugnell
j.strugnell

Reputation: 407

Equivalent to VB AndAlso in SQL?

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.

Upvotes: 5

Views: 15173

Answers (5)

gbn
gbn

Reputation: 432431

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

Upvotes: 6

MikeMuffinMan
MikeMuffinMan

Reputation: 1

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 is made as the conversion fails in dbo.JobIsPending(OrderId) when Orders.OrderId NOT LIKE '%[0-9]%'

tested on SQL Server 2008 R2

Upvotes: 0

EliSherer
EliSherer

Reputation: 1647

Another way is to do:

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

END

Another if after the condition will do an "AndAlso" logic.

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

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

     END

Upvotes: 0

TerrorAustralis
TerrorAustralis

Reputation: 2923

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)

Upvotes: 3

TheSoftwareJedi
TheSoftwareJedi

Reputation: 35226

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

Upvotes: 2

Related Questions