Conditions in T-SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Conditions in T-SQL

Author

Topic

Kezo
Starting Member

1 Post

Posted - 2004-02-08 : 09:46:12

Is second condition executed if first one returnes False (@a = 100)

For example:
IF @a = 100 AND (SELECT ....) = x

Thanks.

Nazim
A custom title

1408 Posts

Posted - 2004-02-08 : 10:18:19

Check Case in BOL

-------------------------
What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson

rihardh
Constraint Violating Yak Guru

307 Posts

Posted - 2004-02-09 : 02:35:45

The catch is in the operator "AND" or "OR" between two conditions. The execution of the condition depends on the used operator.

From BOL:

quote:
AND - Combines two Boolean expressions and returns TRUE when both expressions are TRUE.

quote:
OR - Combines two conditions. When more than one logical operator is used in a statement, OR operators are evaluated after AND operators. However, you can change the order of evaluation by using parentheses.

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-02-09 : 05:27:43

>> Nazim, rihard
Not that Kezo meant I believe.

>> Kezo
In exactly your sample case the answer is YES - the second
condition is evaluated. But it depends. Check these simple
experiments:

declare @a int set @a=7
if @a>777 and 0/0=5
print 'ok'
else
print 'oops'
---------------
oops

if 0/0=5 and @a>777
print 'ok'
else
print 'oops'
---------------
Server: Msg 8134, Level 16, State 1, Line -2122817416
Divide by zero error encountered.
oops

Also,
in the below two samples I intentionally locked table t in other connection
(by begin tran delete from t ....) and only the 2nd IF goes without any delay:

if @a>777 and (select count(*) from t)=0
print 'ok'
else
print 'oops' ----- really oops - the table locked...

if 7>777 and (select count(*) from t)=0
print 'ok'
else
print 'oops'
---------------
oops

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-02-09 : 09:30:34

i think the best answer is: *sometimes* SQL will short-circuit, sometimes it won't. I've seen it do both.

But as a rule, it doesn't hurt that if you have:

IF Cond1 and Cond2 THEN ...

or

IF Cond1 OR Cond2 THEN ...

you might as well put the least expensive check in Cond1, since if it DOES happen to short-circuit, you will get benefit. if it doesn't, no big deal.

It's still not a bad habit to get into for other programming languages that do support it.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources