Check if there are any records in a table which may not exist

Question

I have to check if some records with specific conditions exist in a table which may not exist and I must do this in a scalar function.

Here is my code:

CREATE FUNCTION CheckIfRecordsExistInTestTable()
RETURNS INT
BEGIN
DECLARE @Result INT
SELECT @Result = 
CASE WHEN OBJECT_ID('TestTable') IS NULL THEN 0
ELSE
CASE WHEN EXISTS(SELECT * FROM TestTable) THEN
1
ELSE
0
END
END
RETURN @Result
END

While trying it in SQL Server executing the following statement:

SELECT dbo.CheckIfRecordsExistInTestTable()

Whenever TestTable exists it returns my expected result. But whenever it does not, SQL Server raises an exception (Invalid object name 'TestTable') and I cannot get what I expect (I want a zero return value in this situation). So what do you propose to do for this problem which can be coded to a scalar function?

Answer 1

The other answer gives a correct workaround.

As to why you are getting the problem...

This is a compile time error.

If the statement references a non existent object compilation is deferred until just before execution, but still eventually the whole statement needs to be compiled into an execution plan before it is executed.

This fails when the table doesn't exist and execution of that statement doesn't even begin.

(Execution plan that it tries to create - using a passthru predicate to avoid evaluation of the condition if the CASE not met)

In the workaround the SELECT against testtable is moved into a different statement. Compilation of this statement is still deferred and as the statement is never executed all works fine.