Check the table for any entries that may not exist.

Question

I need to check if there are any records in the table with certain conditions that may not exist, and I have to 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 

When you try to run the following expression in SQL Server:

 SELECT dbo.CheckIfRecordsExistInTestTable() 

Whenever TestTable exists, it returns my expected result. But whenever this does not happen, SQL Server throws an exception (invalid object name " TestTable "), and I cannot get what I expect (in this situation I want to get a null value). So what do you propose to do for this problem, which can be encoded for a scalar function?

Answer 1

Try changing function as follows

 CREATE FUNCTION Checkifrecordsexistintesttable() returns INT BEGIN DECLARE @Result INT IF Object_id('TestTable') IS NULL SET @Result = 0 ELSE SELECT @Result = CASE WHEN EXISTS(SELECT 1 FROM testtable) THEN 1 ELSE 0 END RETURN @Result END; 

To learn more about the cause of the error you receive, check Martin's answer.

Answer 2

Another answer gives the correct workaround.

What is the cause of the problem ...

This is a compile time error.

If the link refers to a non-existent compilation of the object, it is delayed until it is executed, but ultimately the entire statement must be compiled into the execution plan before it is executed.

This fails if the table does not exist, and the execution of this statement does not even begin.

(the execution plan that he is trying to create using the passthru predicate to avoid evaluating the state if CASE has not met)

In a workaround, the SELECT vs. testtable moves to another statement. Compiling this statement is still delayed, and since the statement is never executed, everything is working fine.

Answer 3

follow these steps:

 CREATE FUNCTION CheckIfRecordsExistInTestTable() RETURNS INT BEGIN DECLARE @Result INT SELECT @Result = case when count(1) = 0 then 0 else 1 end from sys.tables where name = 'TestTable' RETURN @result END