Short circuit in IF clause

Question

I have searched and found nothing about it (I believe it is impossible to do it). My problem is that I have to check if a temporary table exists and also if there is some specific data on that temporary table.

Did anyone faced this before? How did you managed to solve it? I would like to avoid creating milions of IF..ELSE blocks.

EDIT:

IF (OBJECT_ID('tempdb..#tempTable') IS NOT NULL 
AND EXISTS (SELECT * FROM #tempTable WHERE THIS_COLUMN = 'value'))
BEGIN
   PRINT 'Temp table and data exists'
END
ELSE
BEGIN
   PRINT 'Temp table or data does not exist'
END

This is what I want to do. The problem comes when the tempTable doesn't exist (that could happen). It throws an error because, although the first stamement returns false, it continues to execute the second statement. And the SELECT statement is not able to find the table and therefore throws the error. The solution I found was to do this:

IF OBJECT_ID('#tempTable') IS NOT NULL
BEGIN
    IF EXISTS (SELECT * FROM #tempTable WHERE THIS_COLUMN = 'value'
    BEGIN
        PRINT 'Temp table and data exists'
    END
    ELSE
    BEGIN
        PRINT 'Temp table exists but data does not exist'
    END
END
ELSE
BEGIN
    PRINT 'Temp table does not exist'
END

My question would be, is there a way of having 2 conditions and if the first condition returns false not check the second one? Kind of using && in a programming language.

Well, I have to check if the temporary table exists and if the specific data exists a lot of times. — Carlos Pastor, Sep 20 '12 at 09:26
exclusive AND? such thing does not exist. Show us some samples of your data and what exactly do you need to check there. — trailmax, Sep 20 '12 at 09:28
The correct term here is **short circuit** not **exclusive and**. — Clodoaldo Neto, Sep 20 '12 at 11:22
Thanks for the clarification, @Clodoaldo. That's exactly what I want to know. — Carlos Pastor, Sep 20 '12 at 11:29

score 3 · Accepted Answer · answered Sep 20 '12 at 11:47

What you are trying to do is not possible as this is a compile time failure and the whole statement needs to be compiled together.

It won't evaluate the first part of the statement then compile the second part only if that is true. You need to split the test for existence and the query referencing the table into two separate statements so they are compiled separately.

score 2 · Answer 2 · edited May 23 '17 at 11:43

2

See here

There is no such thing as XAND logical gate (exclusive AND). In theory XAND would mean, that both operands are true or both are false. So this means XAND is the same as Equals (=), at least for bitwise logical operations.

Please show some sample code to illustrade what you are trying to do.

Regards

edited May 23 '17 at 11:43

Community

1
1

answered Sep 20 '12 at 09:25

LuigiEdlCarno

2,410
2
21
37

score 2 · Answer 3 · answered Sep 20 '12 at 11:29

2

I have searched this sometime ago and if I remember correctly Sql Server does indeed short circuit logical conditions but it is it who decides which one it will check first regardless of the order in which they appear in the if clause.

answered Sep 20 '12 at 11:29

Clodoaldo Neto

118,695
26
233
260

[Sometimes it does, sometimes it doesn't](http://stackoverflow.com/questions/5542927/sql-server-conditional-flow/5543985#5543985) – Martin Smith Sep 20 '12 at 11:39
@MartinSmith does what? Short circuit? If it randomly chooses one of the conditions then it indeed chooses one and you can't control it changing the order in your code. – Clodoaldo Neto Sep 20 '12 at 11:43
Yes, short circuit. My previous comment is a link to an answer showing this. – Martin Smith Sep 20 '12 at 11:45
@MartinSmith Ok. Now I see you have an extensive answer on the subject. – Clodoaldo Neto Sep 20 '12 at 11:47
Thanks both of you for your help! I thought it should be possible but now I see it's not. – Carlos Pastor Sep 20 '12 at 11:58

score 1 · Answer 4 · answered Jul 10 '14 at 23:42

This is probably a sketchy solution, but I sometimes use COALESCE statements to control the sorts of if, else, then structure you are trying to get at. In this case, it is a little more dirty looking because we are looking for the inverse of a coalesce statement.

DECLARE @temp_message AS varchar(100)
SELECT @temp_message = COALESCE(CASE
                                 WHEN OBJECT_ID('tempdb..#tempTable') IS NOT NULL THEN NULL
                                 ELSE 'Temp table does not exist'
                                END,
                                CASE
                                 WHEN EXISTS (SELECT * FROM #tempTable WHERE THIS_COLUMN = 'value') THEN NULL
                                 ELSE 'Specified value does not exist in temp table'
                                END,
                                'Temp table and data exists')

PRINT @temp_message

The COALESCE runs one statement after another until one does not yield a NULL value. That means you can do cool things like run a series of small queries to check some values before running a large costly query. Let me know if that is really illegitimate! It worked on my machine :)

score 0 · Answer 5 · answered Sep 20 '12 at 12:34

I see two ways to get close to this in MSSQL:

First. If you use sp_executesql (dynamic sql) your stored procedure will be compiled without errors. Also if #tempTable doesn't exists server will output error but continue batch execution:

exec sp_executesql N'SELECT count(*) FROM #tempTable WHERE THIS_COLUMN = ''value'''
if @@rowcount > 0 
     print 'ok'
else 
     print 'error'
end;

Second. Just create User defined function with nested IF and EXISTS (as you do it now) which output 0 and 1. And use dynamic sql to input table name and possible filter value(s) to this UDF. In this case you can use this UDF in IF.

This has no advantage here. It's still two statements. If the table doesn't exist the statement is subject to deferred compile anyway so it is only a problem if that statement is actually executed. You would only need this if referencing an existing table that has been modified via an `ALTER TABLE` or performing a `CREATE` that needs to be the only item in the batch. — Martin Smith, Sep 20 '12 at 12:37

Short circuit in IF clause

5 Answers5