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

Answer 1

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

Answer 2

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

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

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.