How do I catch a specific exception type from within a stored procedure?

Question

I am writing a stored procedure which iterates over all of the databases on the server and populates a table variable with an aggregate of the data from some of the different databases. Some databases I'm not interested in as they are irrelevant. The problem is when my CURSOR iterates through those databases I don't care about, a SELECT statement is issued on a table that doesn't exist. How can I ignore the Invalid object name exception and continue with my processing?

Edit:

Here is how I was attempting to skip over databases that were irrelevant:

DECLARE db_cursor CURSOR FOR

SELECT name
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb') 

OPEN db_cursor 

FETCH NEXT FROM db_cursor INTO @currentDatabaseName  

WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @sql = 'SELECT COUNT(Name) FROM ' + @currentDatabaseName + '.sys.Tables WHERE Name = ''SomeTableICareAbout'''

        INSERT INTO @tableSearchResult
        EXEC sp_executesql @sql

        SET @tableCount = (SELECT COUNT(*) FROM @tableSearchResult WHERE TableCount = 1)

        --If the table I care about was found, then do the good stuff
        IF @tableCount > 0
            ...

The problem with this approach is if the executing user (in my case a service account) does not have access to SELECT on the table, then I never know about that error. If the user doesn't have SELECT access, I want that exception to be raised. But, even if the user doesn't have SELECT access, it can SELECT on the sys.Tables view.

You can add a try catch statement, and do something like if ERROR_NUMBER() != 4564 throw http://msdn.microsoft.com/en-us/library/ms175976.aspx 4564 is a made up errornumber I choose, but you should see the error number in the error message you are currently getting. You could also filter out the databases you don't wont, so why even cursor through them? If you post your code, I could make more detailed suggestions. — CandiedCode, Oct 10 '12 at 20:37
@CandiedCode - I would love to skip over the databases entirely, but, I also want to make sure that exceptions are raised if the executing user (service account in my case) doesn't have SELECT privileges on that database. I just posted more code and a description. Thanks for your help so far. — Justin Helgerson, Oct 10 '12 at 20:53

score 4 · Accepted Answer · edited May 23 '17 at 11:56

You can't catch error 208 directly because it's a name resolution error that is raised at compilation time and before the code is actually executed. The behaviour is documented: see the section called "Errors Unaffected by a TRY…CATCH Construct" for an explanation, and the answers to this question have some interesting comments.

In addition to the 'solution' in the documentation, you can use dynamic SQL; the error will be caught in this example:

begin try
    exec('select * from dbo.ThisTableDoesNotExist');
end try
begin catch
    select error_number();
end catch;

If you're looping through all databases, there's a good chance you're using dynamic SQL somewhere anyway, so this might suit your case better.

score 1 · Answer 2 · answered Oct 11 '12 at 14:02

You can catch the error if you are doing it inside a stored procedure (Example documented Here: http://msdn.microsoft.com/en-us/library/ms175976.aspx

Also you can change your dynamic sql to do something like this

SET @sql = '
        If Exists(Select Name From  ' + @currentDatabaseName + '.sys.Tables 
            WHERE Name = ''SomeTableICareAbout'')' --+
        --Add Whatever the Good Stuff is
EXEC sp_executesql @sql

But checking if the table exists first, instead of doing the select count(1) from the table, will prevent that error from being raised.

How do I catch a specific exception type from within a stored procedure?

2 Answers2