August 12, 2010 at 8:00 am
How do i use "orelse" in sql server, "orelse" similar to VB.net
example:
select a,b from table where a=0 or b=0
here if a=0 then sql should never check for b=0
August 12, 2010 at 8:36 am
Ather M (8/12/2010)
How do i use "orelse" in sql server, "orelse" similar to VB.netexample:
select a,b from table where a=0 or b=0
here if a=0 then sql should never check for b=0
This behaviour for evaluation of logical expressions called "short-circuit evaluation". Unlike to Oracle PL/SQL, SQL Server does not gurantee short-circuit evaluation.
Just two examples:
Try:
select * from
sys.objects where [object_id]>0 or [object_id]/0 = 0
You will see (most likely) that as [object_id]>0 always is true no 'Division by zero error' happens as SQL can perform short-circuit evaluation sometimes.
But try:
select * from
sys.objects where [object_id]>0 or exists(select 1 from sys.columns)
Check the query plan. You will see, even that [object_id]>0 is always true, SQL Server will lookup sys.columns...
Talking about first example, even for this one, SQL server may return you 'Division by zero error' sometimes.
August 12, 2010 at 8:36 am
Nothing fancy there it evaluates the conditions in order.
So the first condition (a=0) of the OR is evaluated and it will not look at second condition (b=0) unless the first condition is false.
Select a, b
From #theTable
Where a=0 or b=0
August 12, 2010 at 8:38 am
Ummm, :blink:
DECLARE @TABLE AS TABLE(
A INT,
B INT)
INSERT INTO @TABLE
SELECT 1,1
UNION ALL SELECT 0,1
UNION ALL SELECT 1,0
SELECT a,
b
FROM @TABLE
WHERE ( CASE
WHEN (SELECT COUNT(a)
FROM @TABLE) <> 0 THEN a
ELSE b
END ) = 0
August 12, 2010 at 8:41 am
Mike Nuessler (8/12/2010)
Nothing fancy there it evaluates the conditions in order.So the first condition (a=0) of the OR is evaluated and it will not look at second condition (b=0) unless the first condition is false.
...
Mike, unfortunatley it is not always true!
SQL Server uses short-circuit sometimes, but not always!
Check this out:
http://blogs.msdn.com/b/sqlprogrammability/archive/2006/05/12/596401.aspx
August 12, 2010 at 8:50 am
Eugene Elutin (8/12/2010)
Mike Nuessler (8/12/2010)
Nothing fancy there it evaluates the conditions in order.So the first condition (a=0) of the OR is evaluated and it will not look at second condition (b=0) unless the first condition is false.
...
Mike, unfortunatley it is not always true!
SQL Server uses short-circuit sometimes, but not always!
Check this out:
http://blogs.msdn.com/b/sqlprogrammability/archive/2006/05/12/596401.aspx
Eugene, thanks for the link. Based on the initial post I over-simplified. I just ran a quick test and lo and behold my order of operations has been shattered.
I've learned my new thing for the day before my first coffee, so it's already been a productive day.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply