CODEWITHSUNDEEP
sql-serversql-server-2008
Michael Cherevko
Michael Cherevko

Reputation: 295

How do I check if table exists and it's not empty in one IF

I've tried to do this:

if OBJECT_ID('a_b_Stage2_supA_demB') is not null
and (select COUNT(*) from a_b_Stage2_supA_demB) > 0

But SQL Server returns an error when a_b_Stage2_supA_demB does not exist, because it still tries to check second condition even though first side of "and" condition is already failed.

Thanks for help!

Upvotes: 4

Views: 4819

Answers (4)

StrayCatDBA
StrayCatDBA

Reputation: 2880

DMV's can easily solve your problem:

IF EXISTS (select * from sys.tables t 
    INNER JOIN sys.partitions p on t.object_id = p.object_id 
    WHERE t.name = 'a_b_Stage2_supA_demB' and p.rows > 0)
.....

Upvotes: 3

Vishwanath Dalvi
Vishwanath Dalvi

Reputation: 36671

IF OBJECT_ID('dept') is not NULL
BEGIN
IF exists(SELECT 1 FROM dept)
PRINT 'exists with rows'
END

Upvotes: 0

pyrospade
pyrospade

Reputation: 8070

Unfortunately it's not possible. SQL Server will check to ensure that all object references (within the base code path) exist before compiling. How about no if statements?

begin try
    exec('declare @x int = (
              select 1 / COUNT(*)
              from a_b_Stage2_supA_demB)');
    print 'true';
end try
begin catch
    print 'false';
end catch

Upvotes: 1

Andrey Gordeev
Andrey Gordeev

Reputation: 32529

Just write:

IF OBJECT_ID('a_b_Stage2_supA_demB') is not null
    IF select COUNT(*) from a_b_Stage2_supA_demB > 0
        .....

Upvotes: 0

Related Questions