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.
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
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)
The query engine will take care of this for you. Your query, as written, is fine. All operators will "short circuit" if they can.
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
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
精彩评论