IF condition expression evaluation in TSQL

Question

In programming language like C#, java if a condition has multiple expressions with AND(&&) operator then the second expression is only evaluated if first expression is true.

What about in TSQL? To be specific this is a condition in a Stored procedure in Microsoft Sql server.

IF (exprA AND exprB)

In the above condition if exprA is false then will the exprB is evaluated or not?

"It depends". If it contains `EXISTS` subqueries then pretty much always short circuiting **does not** happen. You can never rely on it happening. You can nest `CASE` expressions or nest `IF` expressions to guarantee it. [I have an open bounty on a similar question at the moment](http://stackoverflow.com/questions/5542927/sql-server-conditional-flow/5543985#5543985) if anyone fancies looking at some additional cases! — Martin Smith, Apr 12 '11 at 14:21

score 2 · Accepted Answer · edited May 23 '17 at 12:04

2

You can't rely on SQL Server not evaluating the second expression if the first one is false. See my answer to the question linked by Martin in his comment.

edited May 23 '17 at 12:04

Community

1
1

answered Apr 12 '11 at 16:32

MicSim

26,265
16
90
133

I think you are right instead of relying on it, I should use nested if or case. Thanks for the information. – JPReddy Apr 13 '11 at 05:13

score 1 · Answer 2 · answered Apr 13 '11 at 05:02

As others have noted, it depends on what your IF conditions are. However, if you are only using simple expressions, it will short circuit. Run the following and you'll see that the divide-by-zero error in the second expression never occurs:

IF (1 = 0 AND (1/0) = 0)
BEGIN
    PRINT 'test1'
END
ELSE
BEGIN
    PRINT 'test2'   
END

score 0 · Answer 3 · answered Oct 01 '14 at 13:54

    create proc ABCDemo1
    (
    @Name varchar(50),
    @date varchar(50)
    )
 as begin
    if(@Name!='' and @date='')
    begin
       select  * from Table1 where condition1
    end
    else if(@Name='' and @date!='')
    begin
       select * from Table2 where condition2
    end
    else if(@Name!='' and @date!='')
    begin
        select * from Table3 where condition3
    end
 end

score 0 · Answer 4 · answered Apr 13 '11 at 03:11

0

The CASE statement seems to allow short-circuiting as shown in the examples below:

-- Conditional Query
select case when  1>0 and 1/0>0 then 1 else 0 end

-- Short Circuited Conditional Query
select case when -1>0 and 1/0>0 then 1 else 0 end

answered Apr 13 '11 at 03:11

pcofre

3,976
18
27

IF condition expression evaluation in TSQL

4 Answers4