Sql: Try/Catch Doesn't Catch an Error When Attempting to Access a Table That It Can't Find

SQL: Try/Catch doesn't catch an error when attempting to access a table that it can't find

At the start of your script use SET XACT_ABORT

SET XACT_ABORT ON

When SET XACT_ABORT is ON, if a Transact-SQL statement raises a
run-time error, the entire transaction is terminated and rolled back.

I don't think that's going to be possible:

The following types of errors are not handled by a CATCH block when
they occur at the same level of execution as the TRY…CATCH construct:

Compile errors, such as syntax errors, that prevent a batch from running.

Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of
deferred name resolution.

Ref.

The following example shows how an object name resolution error
generated by a SELECT statement is not caught by the TRY…CATCH
construct, but is caught by the CATCH block when the same SELECT
statement is executed inside a stored procedure.

USE AdventureWorks2012;
GO

BEGIN TRY
    -- Table does not exist; object name resolution
    -- error not caught.
    SELECT * FROM NonexistentTable;
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH

The error is not caught and control passes out of the TRY…CATCH
construct to the next higher level.

TRY/CATCH not working when table goes missing

Based on the information provided it looks like it may be a problem with your syntax, but it is unclear without a CREATE TABLE statement and some working code. It could also be that you're not checking if the table exists before the SELECT. I just tested the below and it has the desired results.

BEGIN TRY
  BEGIN TRAN
    TRUNCATE TABLE [test_table]
    INSERT INTO [test_table] VALUES ('...')
    SELECT * FROM [test_table]
  COMMIT
END TRY
BEGIN CATCH
  IF (@@TRANCOUNT > 0)
    PRINT N'rolling back transaction' --for confirmation of the catch
    ROLLBACK
END CATCH

Or to avoid the TRY/CATCH use IF EXISTS to check if the table exists before starting anything.

IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'test_table')
BEGIN
  BEGIN TRAN
    TRUNCATE TABLE [test_table]
    INSERT INTO [test_table] VALUES ('...')
    SELECT * FROM [test_table]
  COMMIT
END
ELSE
BEGIN
    -- do something else
    PRINT N'The table does not exist'
END

Hope this helps!

Nested Try/Catches - Does Only Outer Catch Matter (MS SQL)

The answer is in @GarethD's comment. Adding here as the answer so that it can be found by the next victim. I didn't come across that question while searching.

SQL: Try/Catch doesn't catch an error when attempting to access a table that it can't find

GarethD, if you post an answer, I'll mark it as the answer.

SQL How to; Try-Catch error info to error-log table, for stored proc executed within a transaction?

Since transactions are an all or nothing deal, the only way to get done what I'm wanting done, is to call ROLLBACK TRANSACTION inside of the Catch block, before calling my error handling call.

My error log ends up with the error I want logged, but in my .Net catch block, I end up with a complaint from SQL: "Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0."

I can live with that!

TRY CATCH Block in T-SQL

Barring the fact that the "line error occured on" part of any message returned would reference the RAISERROR line and not the line the error actually occured on, there will be no difference. The main reason to do this is as @Chris says, to allow you to programmatically use/manipulate the error data.

How do I catch a query exception in laravel to see if it fails?

The simplest way to catch any sql syntax or query errors is to catch an Illuminate\Database\QueryException after providing closure to your query:

try { 
  $results = \DB::connection("example")
    ->select(\DB::raw("SELECT * FROM unknown_table"))
    ->first(); 
    // Closures include ->first(), ->get(), ->pluck(), etc.
} catch(\Illuminate\Database\QueryException $ex){ 
  dd($ex->getMessage()); 
  // Note any method of class PDOException can be called on $ex.
}

If there are any errors, the program will die(var_dump(...)) whatever it needs to.

Note: For namespacing, you need to first \ if the class is not included as a use statement.

Also for reference:

Laravel 5.5 API - Query Exception

Laravel 8.x API - Query Exception

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

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.

Output parameter value changes depending on error type

I have been through several iterations of this answer, as I have found out more. I think I have now come to a conclusion.

As OP has pointed out, the non-return of the updated parameter is because of the copy-in/copy-out operation of the OUTPUT parameters. However, I have found that a non-existent table error appears not to be picked up by TRY-CATCH inside the SP, but is caught by TRY-CATCH outside.

The following is an amended version of the second example, using TRY..CATCH to pinpoint where the error is picked up.

I used this code to run all variants of the SP:

DECLARE @v int, @result int = 0, @Flag int = 1;
SET @v = 1;
BEGIN TRY
    SET @Flag = 2;
    EXEC @Result = SP @v OUTPUT;
    SET @Flag = 3;
END TRY
BEGIN CATCH
    PRINT ' C:   @Flag=' + CAST( @Flag AS Varchar(10) );

    SET @Flag = 4;

END CATCH

PRINT  ' D:      @v=' + CAST( @v AS Varchar(10) );
PRINT  ' E: @Result=' + CAST( @Result AS Varchar(10) );
PRINT  ' F:   @Flag=' + CAST( @Flag AS Varchar(10) );

The first version of the SP is:

ALTER PROC SP (@p1 int OUTPUT) AS
BEGIN

    PRINT '>> SP';

    SET @p1 = @p1 + 10;

    PRINT '  A: @p1=' + CAST( @P1 as varchar(10) );

    BEGIN

        BEGIN TRY

             SELECT * FROM nonExistentTable; 
        END TRY
        BEGIN CATCH
            SET @p1 = 999;
            RETURN 22;
        END CATCH;  

        RETURN 399;

    END

    PRINT '  B: @p1=' + CAST( @P1 as varchar(10) );

    PRINT '<< SP';

END

The output is:

>> SP
  A: @p1=11
  C:   @Flag=2
  D:      @v=1
  E: @Result=0
  F:   @Flag=4

So it appears that, even though the output parameter @p1 is updated within the SP, the value of the parameter is not passed back to the caller. The non-existent table error is not trapped by the TRY-CATCH inside the SP and by-passes any updates to OUTPUT parameters, but it is caught by the TRY-CATCH outside the SP.

Now, having tested that, I added some additional code into the SP:

ALTER PROC SP (@p1 int OUTPUT) AS
BEGIN

    PRINT '>> SP';

    SET @p1 = @p1 + 10;

    PRINT '  A: @p1=' + CAST( @P1 as varchar(10) );

    IF (EXISTS (SELECT * 
             FROM INFORMATION_SCHEMA.TABLES 
             WHERE TABLE_SCHEMA = 'dbo' 
             AND  TABLE_NAME = 'nonExistentTable'))

    BEGIN

        BEGIN TRY

             SELECT * FROM nonExistentTable; 
        END TRY
        BEGIN CATCH
            SET @p1 = 999;
            RETURN 22;
        END CATCH;  

        RETURN 399;

    END

    PRINT '  B: @p1=' + CAST( @P1 as varchar(10) );

    PRINT '<< SP';

END

with this result:

>> SP
  A: @p1=11
  B: @p1=11
<< SP
 D:      @v=11
 E: @Result=0
 F:   @Flag=3

which is intriguing. So I changed the SP again, this time to select rows from a table that I know contains data:

ALTER PROC SP (@p1 int OUTPUT) AS
BEGIN

    PRINT '>> SP';

    SET @p1 = @p1 + 10;

    PRINT '  A: @p1=' + CAST( @P1 as varchar(10) );

    IF EXISTS( SELECT * FROM Data_Table )
    BEGIN

        BEGIN TRY

             SELECT * FROM nonExistentTable; 
        END TRY
        BEGIN CATCH
            SET @p1 = 999;
            RETURN 22;
        END CATCH;  

        RETURN 399;

    END

    PRINT '  B: @p1=' + CAST( @P1 as varchar(10) );

    PRINT '<< SP';

END

In this case the result is:

>> SP
  A: @p1=11
 C:   @Flag=2
 D:      @v=1
 E: @Result=0
 F:   @Flag=4

Finally a check on a completely unrelated table that is empty:

ALTER PROC SP (@p1 int OUTPUT) AS
BEGIN

    PRINT '>> SP';

    SET @p1 = @p1 + 10;

    PRINT '  A: @p1=' + CAST( @P1 as varchar(10) );

    IF EXISTS( SELECT * FROM Empty_Table )
    BEGIN

        BEGIN TRY

             SELECT * FROM nonExistentTable; 
        END TRY
        BEGIN CATCH
            SET @p1 = 999;
            RETURN 22;
        END CATCH;  

        RETURN 399;

    END

    PRINT '  B: @p1=' + CAST( @P1 as varchar(10) );

    PRINT '<< SP';

END

with the result:

>> SP
  A: @p1=11
  B: @p1=11
<< SP
 D:      @v=11
 E: @Result=0
 F:   @Flag=3

Someone who knows more about how the internals of SQL Server work might be able to explain and, perhaps, justify these results.

Sql: Try/Catch Doesn't Catch an Error When Attempting to Access a Table That It Can't Find