How do i use "orelse" in sql server, "orelse" similar to VB.net

Question

How do i use "orelse" in sql server, "orelse" similar to VB.net

Ather M

SSChasing Mays

Points: 607
More actions
August 12, 2010 at 8:00 am

#226021

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 1

Ather M (8/12/2010)
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

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.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Mike Nuessler Right there with Babe Points: 788 More actions · Answer 2

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

In the beginning the Universe was created. This has made a lot of people very angry and has been widely regarded as a bad move. Douglas Adams (1952-2001)

Cadavre SSC-Forever Points: 41690 More actions · Answer 3

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

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 4

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

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Mike Nuessler Right there with Babe Points: 788 More actions · Answer 5

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.

In the beginning the Universe was created. This has made a lot of people very angry and has been widely regarded as a bad move. Douglas Adams (1952-2001)