SQL Server TRY CATCH FINALLY

Question

I have a scenario where I need something similar to .NET's try-catch-finally block.

On my try, I will CREATE a #temp table, INSERT data to it & process other data sets based on #temp.

On CATCH then RAISERROR. Is it possible to have a FINALLY block to DROP #temp? Below is the pseudo code:

BEGIN TRY

  CREATE TABLE #temp
  (
     --columns
  )
  --Process data with other data sets

END TRY
BEGIN CATCH

  EXECUTE usp_getErrorMessage

END CATCH
BEGIN FINALLY

  DROP TABLE #temp

END FINALLY

Nesmith

possible duplicate of Finally Clause in SQL Server Transaction? Something that will execute irrespective of success or failure? – Trembly

No, there is no FINALLY. Did you see any reference to it in the documentation? Are you sure you want to explicitly drop your #temp tables anyway? – Testudo

Answer 1

Instead of creating a table you could just declare a table variable (which will automatically go away when the query ends).

BEGIN TRY
DECLARE @temp TABLE
(
    --columns
)
--do stuff
END TRY
BEGIN CATCH
--do other stuff
END CATCH

Suint

This is a good approach when the data you are holding is small or you do not need sql server to perform any optimization as tables variables do not create statistics etc.. While it solves the issue of insuring that it is always cleaned up, it would not solve it if they really needed to use a temp table as shown in the example. I hate using the GOTO statement but in T-SQL I am not sure how else you would achieve it. GOTO l_Exit l_Exit: DROP TABLE #temp – Appraise

I just now faced a problem when table variable in some reason is stuck. Query itself executed in 2 sec, temp table in 3 seconds, but table variable could not be performed at all, I canceled it after 10 minutes execution time ... – Araliaceous

Good example. However, keep in mind that variable tables are not always the most efficient when manipulating large amounts of data. – Livery

The try/catch approach can't manage with common resource allocation/dealocation tasks such as sp_OACreate/sp_OADestroy, sp_xml_preparedocument/sp_xml_removedocument, session management in HTTP API and so on. Especially when they are executed jointly. Thefinally concept of exception handalling is required indeed. – Damson

Answer 2

While not exactly the same as FINALLY, the T-SQL version of Try-Catch does allow that code that needs execute after both the Try and Catch blocks can occur after the end of the END CATCH statement. Using the question code as an example:

    BEGIN TRY
      CREATE TABLE #temp
       (
         --columns
       )
      --Process data with other data sets
    END TRY
    BEGIN CATCH
    EXECUTE usp_getErrorMessage
    END CATCH;

IF OBJECT_ID('tempdb..#temp') IS NOT NULL -- Check for table existence
    DROP TABLE #temp;

The DROP TABLE command will execute whether the Try or Catch execute. See: BOL Try...Catch

Giraud

using Object_ID() function with #tables – Deathlike

This is the correct answer to this particular question. – Locust

SQL Server 2016 or higher, you can do the DROP TABLE IF EXISTS #temp one-liner instead of using an IF statement with OBJECT_ID. – Preexist

Doesn't this fail to drop the table if 'EXECUTE usp_getErrorMessage' throws an error? – Falgoust

@CursedGoat, the IF block executes regardless of what happens within the Try/Catch. It has the IF because if the temp table didn't get created and you just used the DROP statement, that could throw an error. – Giraud

The reason this works is that T-SQL does not stop execution when an error is thrown. If you run the following query: DECLARE @x INT DECLARE @y INT DECLARE @z INT SET @x = 5 SET @y = 0 SET @z = @x / @y SELECT @x, @y, @z you will notice that the select still executes even though a divide by zero error occurs. For other languages like C#, java, etc an uncaught exception will end program execution. – Tap

Answer 3

there is no FINALLY equivalent.
an alternative may be table variables but is not exactly the same and must be evaluated on a case by case basis.
there is a SO question with details very useful to make an informed choice.
with table variables you don't need to clean up like you do with temp tables

Katricekatrina

An alternative to temp tables may be temp tables? I think you meant table variables. Also this is a much better question with a lot more details. – Testudo

Answer 4

"FINALLY" is often, but not always, functionally identical to having the "final" code follow the TRY/CATCH (without a formal "FINALLY" block). Where it is different is the case where something in the TRY/CATCH blocks could cause execution to end, such as a return statement.

For example, a pattern I've used is to open a cursor, then have the cursor-using code in the TRY block, with the cursor close/deallocate following the TRY/CATCH block. This works fine if the blocks won't exit the code being executed. However, if the TRY CATCH block does, for example, a RETURN (which sounds like a bad idea), if there were a FINALLY block, it would get executed, but with the "final" code placed after the TRY / CATCH, as T-SQL requires, should those code blocks cause the execution to end, that final code won't be called, potentially leaving an inconsistent state.

So, while very often you can just put the code after the TRY/CATCH, it will be a problem if anything in those blocks could terminate without falling through to the cleanup code.

Raina

Answer 5

Local temp tables (e.g., "#Temp") are automatically dropped when the SQL connection ends. It's good practice to include an explicit DROP command anyway, but if it doesn't execute, the table will still be dropped.

If you must ensure that a DROP executes as soon as possible, you'll have to repeat the DROP command in a CATCH clause, since there's no FINALLY:

-- create temp table;
BEGIN TRY
    -- use temp table;
    -- drop temp table;
END TRY
BEGIN CATCH
    -- drop temp table;
    THROW;  -- rethrow the error
END CATCH

Table variables are an alternative: they're dropped when the variable goes out of scope. However, table variables do not support statistics, so if the table variable is large and used in multiple queries, it may not perform as well as a temp table.

Arie

Answer 6

With T-SQL, code placed after the TRY-CATCH block will get executed. So the answer to the question is simply to add a DROP TABLE IF EXISTS #temp right after the END CATCH like so:

BEGIN TRY
  CREATE TABLE #temp(
     --columns
  )
  --Process data with other data sets
END TRY
BEGIN CATCH
  EXECUTE usp_getErrorMessage
END CATCH
-- Anything under this line will execute regardless of if the code reached the CATCH block or not.
DROP TABLE IF EXISTS #temp;  -- Works in SQL Server 2016+, for older versions see here: https://mcmap.net/q/364802/-sql-server-try-catch-finally

Preexist

Answer 7

using custom error number to indicate there no real error, just final code?

-- create temp table;
BEGIN TRY
    -- use temp table;
    THROW 50555;
END TRY
BEGIN CATCH
    -- drop temp table;
    IF ERROR_NUMBER() <> 50555
        THROW;  -- rethrow the error
END CATCH

Dirge

Answer 8

The correct answer in this case is the one proposed by @Dave Bennett; after the TRY/CATCH block check for the existence of the table and drop it.

But what if you are raising an exception out of your CATCH and you need to do some "FINALLY" type processing?

Could it be as simple as setting a variable in the CATCH and checking it after you fall out of the CATCH?

DECLARE @is_error BIT = 0;

BEGIN TRY

  --Process data with other data sets

END TRY
BEGIN CATCH
  -- Your exception handling code here
  
  SET @is_error = 1;

END CATCH

-- Your "FINALLY" code here.

-- Then Check if you need to RAISERROR
IF @is_error = 0
BEGIN
    -- Your success code 
END
ELSE
BEGIN
    -- Your fail code
    -- RAISERROR
END;

Locust

Could it be as simple as...? -- That's is a question, not an answer. If you tested the code and confirmed that it works then please edit your answer accordingly, otherwise this will be flagged as "not an answer". – Higa