SQL Server 最终尝试捕获

SQL Server TRY CATCH FINALLY

提问by kissta

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

我有一个场景，我需要类似的东西 .NET's try-catch-finally block.

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

在我的尝试，我会CREATE a #temp table，INSERT数据，将其与基于处理其他数据集#temp。

On CATCHthen RAISERROR. Is it possible to have a FINALLYblock to DROP #temp? Below is the pseudo code:

在CATCH随后RAISERROR。有可能有一个FINALLY块DROP #temp吗？下面是伪代码：

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

采纳答案by Becuzz

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

回答by Dave Bennett

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:

虽然与 FINALLY 不完全相同，但 Try-Catch 的 T-SQL 版本确实允许需要在 Try 和 Catch 块之后执行的代码可以在 END CATCH 语句结束后出现。以问题代码为例：

    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

无论 Try 还是 Catch 执行，DROP TABLE 命令都会执行。请参阅：BOL Try...Catch

回答by Paolo

there is no FINALLYequivalent.
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 questionwith details very useful to make an informed choice.
with table variables you don't need to clean up like you do with temp tables

没有FINALLY等价物。
另一种可能是表变量，但并不完全相同，必须根据具体情况进行评估。
有一个SO questionwith details 对于做出明智的选择非常有用。
使用表变量，您不需要像使用临时表一样进行清理

回答by RBerman

"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.

“FINALLY”通常（但不总是）在功能上与 TRY/CATCH 后面的“final”代码相同（没有正式的“FINALLY”块）。不同之处在于 TRY/CATCH 块中的某些内容可能导致执行结束，例如 return 语句。

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 werea FINALLY block, it wouldget 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'tbe called, potentially leaving an inconsistent state.

例如，我使用的一种模式是打开一个游标，然后在 TRY 块中使用游标代码，在 TRY/CATCH 块之后关闭/解除分配游标。如果块不会退出正在执行的代码，这可以正常工作。但是，如果try catch块呢，例如，RETURN（它像一个坏主意的声音），如果是一个finally块，它会得到执行，但与“最终”的代码放在try / catch语句之后，如T-SQL 要求，如果这些代码块导致执行结束，则不会调用最终代码，从而可能留下不一致的状态。

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.

因此，虽然通常您可以将代码放在 TRY/CATCH 之后，但如果这些块中的任何内容都可以终止而不会落入清理代码，那将是一个问题。

回答by Brian Harrison

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.

当 SQL 连接结束时，本地临时表（例如，“#Temp”）会自动删除。无论如何，包含显式 DROP 命令是一种很好的做法，但如果它不执行，该表仍将被删除。

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

如果必须确保 DROP 尽快执行，则必须在 CATCH 子句中重复 DROP 命令，因为最终没有： -- create temp table; 开始尝试——使用临时表；-- 删除临时表；END TRY BEGIN CATCH -- 删除临时表；扔; -- 重新抛出错误 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.

表变量是另一种选择：当变量超出范围时，它们会被删除。但是，表变量不支持统计，因此如果表变量很大并且用于多个查询，它的性能可能不如临时表。