How to force a SQL statement to do a different select if a table is missing instead of throwing the 'invalid object name' error?

Question

I have a SQL statement that I run against a large number of SQL Server instances in order to collect data about the service account. The SQL statement includes a management view that is not available in all versions of SQL Server, and when it's not available, I want it to return 'Data not available' rather than error out. This seems like it would be an easy case for try/catch or if/else, but it isn't. Instead, the SQL Server engine evaluates it and throws the 'Invalid object name' error before entering into the try/catch or if/else logic.

Examples:

-- Example 1
IF EXISTS (SELECT * FROM sys.dm_server_services) 
BEGIN
   SELECT * FROM sys.dm_server_services 
END
ELSE
BEGIN
    SELECT 'No Data'
END

-- Example 2

BEGIN
    BEGIN TRY
        SELECT * FROM sys.dm_server_services
    END TRY
    BEGIN CATCH
        SELECT  
            'No data available' as Service_Account
            ,ERROR_NUMBER() AS ErrorNumber  
             ,ERROR_MESSAGE() AS ErrorMessage;  
    END CATCH
END;
GO

In both cases, sys.dm_server_services gets a red squiggly line and an invalid object error when ran, instead of evaluating as false and then doing the second part of the logic.

Any tips?

Zhorov · Answer 1 · 2019-11-15T15:20:27.423

3

One possible approach is to use OBJECT_ID() and a dynamic statement.

IF OBJECT_ID ('sys.dm_server_services') IS NULL
BEGIN
   --SELECT 1
   EXEC sp_executesql N'SELECT ''No data'' '
END
ELSE
BEGIN
    --SELECT 2
    EXEC sp_executesql N'SELECT * FROM sys.dm_server_services'
END

edited Nov 15 '19 at 15:20

answered Nov 15 '19 at 14:03

Zhorov

28,486
6
27
52

Thanks that is a very good point with object_id. The only problem that I should have included is that I would probably have my else in your example select from this object, and by introducing a select on that object, and error would be thrown before it has a chance to do the first Select 1 instead. – datadawg2000 Nov 15 '19 at 14:09
Fairly certain this approach doesn't work. The object definitions still get resolved inside the batch before the actual IF statement is run. Now you could do the inner clause as dynamic SQL. Or have it call a procedure/function. So evaluation is done seperately. – D Kramer Nov 15 '19 at 14:20

score 0 · Answer 2 · answered Nov 15 '19 at 14:21

0

-- find if the object exists
IF EXISTS (
    SELECT 1
    FROM sys.all_objects o WITH (NOLOCK)
        JOIN sys.schemas s WITH (NOLOCK)
            ON s.schema_id = o.schema_id
    WHERE o.name = 'dm_server_services'
        AND s.name = 'sys'

) BEGIN
    --execute the query using dynamic SQL
    EXEC sp_executesql N'SELECT * FROM sys.dm_server_services';

END ELSE BEGIN
    SELECT 'No Data'
END

answered Nov 15 '19 at 14:21

Denis Rubashkin

2,151
1
9
14

this is the answer that worked perfectly. By offloading to the sp_executesql proc, the db engine doesn't yell at my about the invalid object before I have a chance to explain myself. – datadawg2000 Nov 15 '19 at 16:36

score -1 · Answer 3 · answered Nov 15 '19 at 14:09

You could try using the INFORMATION_SCHEMA. There you have all the information about tables and views contained into the server.

Example:

SELECT *
FROM sys.INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME= "dm_server_services"

Would return something like

TABLE_CATALOG   TABLE_SCHEMA   TABLE_NAME               TABLE_TYPE
sys             dbo            dm_server_services       BASE TABLE

Then you can use the EXISTS() function to check whether this query returns results and modify your query accordingly.

So your example could become:

IF EXISTS (SELECT * 
           FROM sys.INFORMATION_SCHEMA.TABLES
           WHERE TABLE_NAME= "dm_server_services") 
BEGIN
   SELECT 1 
END
ELSE
BEGIN
    SELECT 2
END

Source used: https://chartio.com/resources/tutorials/sql-server-list-tables-how-to-show-all-tables/

score -1 · Answer 4 · answered Nov 15 '19 at 14:19

> BEGIN
>     BEGIN TRY
>         SELECT (1/0) counts,* FROM sys.dm_server_services
>     END TRY
>     BEGIN CATCH
>         SELECT  
>             'No data available' as Service_Account
>             ,ERROR_NUMBER() AS ErrorNumber  
>              ,ERROR_MESSAGE() AS ErrorMessage;  
>     END CATCH END; 
GO

This query is working as your expected if exception is caught

also gives output as:

|Service_Account   | ErrorNumber    |ErrorMessage                     |   
|------------------|----------------|---------------------------------|  
|No data available |8134            |Divide by zero error encountered.|

When I run this on a server that doesn't have dm_server_services, it gives the invalid object error instead of going into the catch like your results do. Did you run this on a version of sql that has the dm_server_services view? This is the problem I run into, it throws the error before even looking at the first BEGIN here. — datadawg2000, Nov 15 '19 at 15:56

How to force a SQL statement to do a different select if a table is missing instead of throwing the 'invalid object name' error?

4 Answers4