CASE WHEN SQL Query executes condition in else even if first condition is true

I’m trying to check if data exists in a table. First I check whether the table exists then I use a SELECT statement to check whether the table is empty or not

DECLARE @dataExists BIT = 0

SELECT 
             @dataExists = CASE 
                               WHEN OBJECT_ID('TableName') IS NULL 
                                  THEN 0 
                                  ELSE CASE 
                                          WHEN EXISTS(SELECT 1 FROM TableName) 
                                             THEN 1 
                                             ELSE 0 
                                       END 
                            END

SELECT @dataExists

I have this query which is not supposed to execute SELECT 1 FROM TableName if OBJECT_ID('TableName') returns NULL but this query goes on and executes

SELECT 1 FROM TableName 

which of course throws an error

Invalid Object Name TableName

Answer

Try the following:

DECLARE @dataExists BIT = 0

IF OBJECT_ID('TableName') IS NOT NULL
BEGIN
    SET @dataExists = (SELECT TOP 1 1 FROM TableName)
END

SELECT @dataExists

The error you are getting is a compilation error. Since it’s a single statement. SQL Server needs to build an execution plan, and can’t do this, if the table doesn’t exists.

By adding an IF the statement doesn’t need to be “compiled” if the condition is not met.

With dynamic SQL:

DECLARE @TableName SysName = 'TableName'
DECLARE @Sql NVARCHAR(MAX) = '
DECLARE @dataExists BIT = 0

IF OBJECT_ID('''+@TableName+''') IS NOT NULL
BEGIN
    SET @dataExists = (SELECT TOP 1 1 FROM '+QUOTENAME(@TableName)+')
END

SELECT @dataExists
'

EXEC (@Sql)