blocks|key|4579869|text|您可以只声明一个表变量(当查询结束时，该变量将自动消失)，而不是创建表。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4579870|BEGIN+TRY
DECLARE+@temp+TABLE
(
++++--columns
)
--do+stuff
END+TRY
BEGIN+CATCH
--do+other+stuff
END+CATCH|code-block|syntax|javascript|4579871|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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

<pre><code>BEGIN TRY
DECLARE @temp TABLE
(
 --columns
)
--do stuff
END TRY
BEGIN CATCH
--do other stuff
END CATCH
</code></pre>

blocks|key|73530|text|没有FINALLY等效项。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|73531|替代方案可以是表变量，但并不完全相同，必须根据具体情况进行评估。|73532|有一个包含详细信息的SO+question，对于做出明智的选择非常有用。|73533|使用表变量，您不需要像使用临时表那样进行清理|73534|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/11857789/when-should-i-use-a-table-variable-vs-temporary-table-in-sql-server^0|2|7|0|0|A|B|0|0|0^^$0|@$1|2|3|4|5|6|7|T|8|@$9|U|A|V|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|W|8|@]|D|@]|E|$]]|$1|H|3|I|5|6|7|X|8|@]|D|@$9|Y|A|Z|1|10]]|E|$]]|$1|J|3|K|5|6|7|11|8|@]|D|@]|E|$]]|$1|L|3|-4|5|6|7|12|8|@]|D|@]|E|$]]]|M|$N|$5|O|P|Q|E|$R|S]]]]

there is no <code>FINALLY</code> 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 <a href="https://stackoverflow.com/questions/11857789/when-should-i-use-a-table-variable-vs-temporary-table-in-sql-server">SO question</a> 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

blocks|key|73563|text|虽然与FINALLY不完全相同，但Try-Catch的T-SQL版本确实允许在Try和Catch块之后需要执行的代码可以在end+CATCH语句结束之后执行。以问题代码为例：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|73564|++++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;|code-block|syntax|javascript|73565|DROP+TABLE命令将执行Try或Catch。请参阅：BOL+Try...Catch|offset|length|73566|entityMap|0|LINK|mutability|MUTABLE|url|https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&sqi=2&ved=0CB4QFjAA&url=https%253A%252F%252Fmsdn.microsoft.com%252Fen-us%252Flibrary%252Fms175976.aspx&ei=AU2UVeqaB4Lk-AHh5oC4BA&usg=AFQjCNG95BgubG5mT4Fyl9ggH3t_7hkFoA&sig2=OV4OAJVg5Xo3ibM04LLrbA&bvm=bv.96952980,d.cWw&cad=rja^0|0|0|T|F|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@$I|V|J|W|1|X]]|A|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

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:

<pre><code> 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;
</code></pre>

The DROP TABLE command will execute whether the Try or Catch execute.
See: <a href="https://www.google.com/url?sa=t&amp;rct=j&amp;q=&amp;esrc=s&amp;source=web&amp;cd=1&amp;sqi=2&amp;ved=0CB4QFjAA&amp;url=https%3A%2F%2Fmsdn.microsoft.com%2Fen-us%2Flibrary%2Fms175976.aspx&amp;ei=AU2UVeqaB4Lk-AHh5oC4BA&amp;usg=AFQjCNG95BgubG5mT4Fyl9ggH3t_7hkFoA&amp;sig2=OV4OAJVg5Xo3ibM04LLrbA&amp;bvm=bv.96952980,d.cWw&amp;cad=rja" rel="noreferrer">BOL Try...Catch</a>

blocks|key|4579988|text|当SQL连接结束时，本地临时表(例如，"#+temp+")将自动删除。不管怎样，最好包含一个显式的DROP命令，但是如果它不执行，表仍然会被删除。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|4579989|如果必须确保DROP尽快执行，则必须在CATCH子句中重复DROP命令，因为没有FINALLY|4579990|--+create+temp+table;
BEGIN+TRY
++++--+use+temp+table;
++++--+drop+temp+table;
END+TRY
BEGIN+CATCH
++++--+drop+temp+table;
++++THROW;++--+rethrow+the+error
END+CATCH|code-block|syntax|javascript|4579991|表变量是另一种选择:当变量超出作用域时，它们会被删除。但是，表变量不支持统计，因此如果表变量很大，并且在多个查询中使用，那么它的性能可能不如临时表。|4579992|entityMap^0|1D|4|0|6|4|J|5|T|4|14|7|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|T|8|@$9|U|A|V|B|C]|$9|W|A|X|B|C]|$9|Y|A|Z|B|C]|$9|10|A|11|B|C]]|D|@]|E|$]]|$1|H|3|I|5|J|7|12|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|13|8|@]|D|@]|E|$]]|$1|O|3|-4|5|6|7|14|8|@]|D|@]|E|$]]]|P|$]]

Local temp tables (e.g., &quot;#Temp&quot;) are automatically dropped when the SQL connection ends. It's good practice to include an explicit <code>DROP</code> command anyway, but if it doesn't execute, the table will still be dropped.
If you must ensure that a <code>DROP</code> executes as soon as possible, you'll have to repeat the <code>DROP</code> command in a <code>CATCH</code> clause, since there's no <code>FINALLY</code>:
<pre><code>-- create temp table;
BEGIN TRY
 -- use temp table;
 -- drop temp table;
END TRY
BEGIN CATCH
 -- drop temp table;
 THROW; -- rethrow the error
END CATCH
</code></pre>
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.

blocks|key|4580017|text|使用自定义错误号来表示没有真正的错误，只有最终代码？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4580018|--+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|code-block|syntax|javascript|4580019|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

using custom error number to indicate there no real error, just final code?
<pre><code>-- create temp table;
BEGIN TRY
 -- use temp table;
 THROW 50555;
END TRY
BEGIN CATCH
 -- drop temp table;
 IF ERROR_NUMBER() &lt;&gt; 50555
 THROW; -- rethrow the error
END CATCH
</code></pre>

blocks|key|73659|text|在这种情况下，正确的答案是@Dave+Bennett提出的；在TRY/CATCH块之后，检查表是否存在并删除它。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|73660|但是，如果您要在捕获之外引发异常，并且需要执行一些“最终”类型处理，该怎么办？|73661|它能像在CATCH中设置一个变量并在您从CATCH中掉出来后检查它一样简单吗？|73662|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;|code-block|syntax|javascript|73663|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|O|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|P|8|@]|9|@]|A|$I|J]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

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 &quot;FINALLY&quot; type processing?
Could it be as simple as setting a variable in the CATCH and checking it after you fall out of the CATCH?
<pre><code>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 &quot;FINALLY&quot; 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;
</code></pre>

blocks|key|73692|text|使用T-SQL，放置在TRY-CATCH块之后的代码将被执行。所以这个问题的答案很简单，就是在END+CATCH后面添加一个DROP+TABLE+IF+EXISTS+#temp，如下所示：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|73693|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%2B,+for+older+versions+see+here:+https://stackoverflow.com/a/31171650/15596537|code-block|syntax|javascript|73694|entityMap^0|B|9|1B|9|1Q|Q|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]|$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|U|8|@]|D|@]|E|$]]]|L|$]]

With T-SQL, code placed after the <code>TRY-CATCH</code> block will get executed. So the answer to the question is simply to add a <code>DROP TABLE IF EXISTS #temp</code> right after the <code>END CATCH</code> like so:
<pre class="lang-sql prettyprint-override"><code>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://stackoverflow.com/a/31171650/15596537
</code></pre>

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

On my try, I will <code>CREATE a #temp table</code>, <code>INSERT</code> data to it &amp; process other data sets based on <code>#temp</code>.

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

<pre><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
</code></pre>

SQL Server TRY CATCH FINALLY

SQL Server

我有一个场景，我需要类似于.NET's try-catch-finally block.的东西在我的尝试中，我将CREATE a #temp table，INSERT数据到它&处理其他基于#temp的数据集。在CATCH上，然后在RAISERROR上。有没有可能对DROP #temp使用FINALLY块？下面是伪代码：...

问SQL Server尝试最终捕获
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL Server尝试最终捕获EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL Server尝试最终捕获
EN