Is there an equivalent to VB's
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?
The only way to guarantee the order of evaluation is to use CASE
WHEN @a IS NULL THEN 1
WHEN a = @a THEN 0
END = 1
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
The query engine will take care of this for you. Your query, as written, is fine. All operators will "short circuit" if they can.
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)
Another way is to do:
IF (@a > 0) IF (@a = 5)
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)
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