why does sql server execute the second statement redundantly?

Question

I have the following which throws an error if the table Tru.Repo does not exist:

IF (NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'Tru' AND TABLE_NAME = 'Repo')) OR 
   (NOT EXISTS (SELECT * FROM Tru.Repo WHERE RepoName = 'Tru.Repo'))
BEGIN
    print('in')
END

but I would expect that if Tru.Repo does not exist, the first statement is true so why does it then execute the second statement? Is there anyway of getting around this using a single IF block?

http://stackoverflow.com/questions/5542927/sql-server-conditional-flow — David Stetler, Jul 02 '14 at 22:13

score 2 · Accepted Answer · answered Jul 02 '14 at 22:06

2

T-SQL is interpreted, so it'll want to make sure everything is valid first. You could solve it with dynamic SQL, but that's quite messy.

answered Jul 02 '14 at 22:06

Rob Farley

15,625
5
44
58

score 1 · Answer 2 · answered Jul 03 '14 at 02:55

The problem is not that the second statement is executed. The "problem" is that the entire statement is compiled before it is run.

So, the second statement is compiled. And, you get a compilation error when the table doesn't exist.

Here is an approach to solving this problem:

IF (NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'Tru' AND TABLE_NAME = 'Repo'))
BEGIN
    print('in')
END
ELSE
BEGIN
    declare @retval int = 0;
    exec sp_execute_sql N'SELECT @retval = 1 FROM Tru.Repo WHERE RepoName = ''Tru.Repo''',
                        N'@retval int output', @retval = @retval;
    if @retval = 0
    BEGIN
        print('in')
    END
END;

why does sql server execute the second statement redundantly?

2 Answers2