Preferred Method to Catch Specific OleDB Error

Question

Ok - I have a situation in which I must execute a dynamically built stored procedure against tables that may, or may not be in the database. The data retrieved is then shunted to a VB.Net backed ASP based report page. By design, if the tables are not present in the database, the relevant data is automatically hidden on the report page. Currently, I'm doing this by checking for the inevitable error, and hiding the div in the catch block. A bit kludgy, but it worked.

I can't include the VB code-behind, but the relevant stored procedure is included below.

However, a problem with this method was recently brought to my attention when, for no apparent reason, the div was being hidden even though the proper data was available. As it turned out, the user trying to select the table in the dynamic SQL call didn't have the proper select permissions, an easy enough fix once I could track it down.

So, two fold question. First and foremost - is there a better way to check for a missing table than through catching the error in the VB.Net codebehind? All things considered, I'd rather save the error checking for an actual error. Secondly, is there a preferred method to squirrel out a particular OLE DB error out of the general object caught by the try->catch block other than just checking the actual stack trace string?

SQL Query - The main gist of the code is that, due to the design of the database, I have to determine the name of the actual table being targeted manually. The database records jobs in a single table, but each job also gets its own table for processing data on the items processed in that job, and it's data from those tables I have to retrieve. Absolutely nothing I can do about this setup, unfortunately.

DECLARE   @sql                  NVarChar(Max),
          @params               NVarChar(Max),
          @where                NVarChar(Max)

-- Retained for live testing of stored procedure.
-- DECLARE @Table NvarChar(255) SET @Table = N'tblMSGExportMessage_10000'
-- DECLARE @AcctID Integer SET @AcctID = 10000
-- DECLARE @Type Integer SET @Type = 0 -- 0 = Errors only, 1 = All Messages
-- DECLARE @Count Integer

-- Sets our parameters for our two dynamic SQL calls.
SELECT @params = N'@MsgExportAccount INT, @cnt INT OUTPUT'

-- Sets our where clause dependent upon whether we want all results or just errors.
IF @Type = 0
    BEGIN
          SELECT @where =
          N'   AND ( mem.[MSGExportStatus_OPT_CD] IN ( 11100, 11102 ) ' +
          N'      OR mem.[IngestionStatus_OPT_CD] IN ( 11800, 11802, 11803 ) ' +
          N'      OR mem.[ShortcutStatus_OPT_CD] IN ( 11500, 11502 ) ) '
     END
ELSE
     BEGIN
          SELECT @where = N' '
     END

-- Retrieves a count of messages.
SELECT @sql =
N'SELECT @cnt = Count( * ) FROM dbo.' + QuoteName( @Table ) + N' AS mem ' +
N'WHERE mem.[MSGExportAccount_ID] = @MsgExportAccount ' + @where

EXEC sp_executesql @sql, @params, @AcctID, @cnt = @Count OUTPUT

Answer 1

To avoid an error you could query the sysobjects table to find out if the table exists. Here's the SQL (replace YourTableNameHere). If it returns > 0 then the table exists. Create a stores procedure on the server that runs this query.

select count(*)
from sysobjects a with(nolock) 
where a.xtype = 'U'
    and a.name = 'YourTableNameHere'