插入失败后如何继续执行当前块?

本文介绍了插入失败后如何继续执行当前块?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

考虑下表:

CREATE TABLE [dbo].[OrderingTest]( [ColKey] [int] IDENTITY(1,1) NOT NULL, [Col0] [int] NULL, [Col1] [int] NOT NULL, [Col2] [int] NOT NULL ) ON [PRIMARY]

...以及以下批次:

BEGIN INSERT INTO OrderingTest(Col0,Col1,Col2) VALUES (1,NULL,3); SELECT SCOPE_IDENTITY(); END;

Test 表不允许 Col1 中的 NULL 值，因此插入将失败.我希望此时使用 SCOPE_IDENTITY() 的值来测试失败.但是，不是看到 SELECT SCOPE_IDENTITY() 生成带有 NULL 列作为输出的单行，批处理的执行在插入失败后终止，SELECTSCOPE_IDENTITY() 行永远不会执行.

The Test table does not allow NULL values in Col1, so the insert will fail. I was hoping to test for failure using the value of SCOPE_IDENTITY() at this point. However, instead of seeing the SELECT SCOPE_IDENTITY() produce a single row with a NULL column as output, execution of the batch terminates after the failed insert and the SELECT SCOPE_IDENTITY() line is never executed.

更新:

我错了，SCOPE_IDENTITY() 的结果显示在结果窗格中.我很困惑，因为 SSMS 将消息窗格切换到焦点而不是结果窗格(因为发生了错误).

I was incorrect the result of SCOPE_IDENTITY() was displayed in the Results pane. I was confused, because SSMS switched the Messages pane into focus instead of the Results pane (since an error occurred).

但是，在 INSERT 失败后，将显示标识列的最新值 ColKey 而不是 NULL.我认为如果 INSERT 失败，SCOPE_IDENTITY() 应该返回 NULL.

However the latest value of the identity column, ColKey, is displayed instead of NULL after a failed INSERT. I thought SCOPE_IDENTITY() is supposed to return NULL if an INSERT fails.

推荐答案

我尝试了这些命令:

BEGIN DECLARE @theKey INT BEGIN TRY INSERT INTO OrderingTest(Col0,Col1,Col2) VALUES (1,NULL,3); SELECT @theKey = SCOPE_IDENTITY(); END TRY BEGIN CATCH SET @theKey = - 1 END CATCH select @theKey END;

以下内容

BEGIN DECLARE @theKey INT SET @theKey = 1 INSERT INTO OrderingTest(Col0,Col1,Col2) VALUES (1,NULL,3); SELECT @theKey = SCOPE_IDENTITY(); select @theKey END;

在这两种情况下，都执行了 SELECT @theKey，尽管在​​第二个示例中，打印窗口显示了一条错误消息.请注意，在您的示例中，您尝试插入 TEST 表，而不是 OrderingTest 表.这将产生错误并且不会运行 SELECT.你确定你的例子是对的吗?

In both cases, the SELECT @theKey was executed, although in the second example, an error message was displayed to the print window. Note that in your example, you are trying to insert into the TEST table, rather than OrderingTest table. This will produce an error and not run the SELECT. Are you sure your example is right?

请注意，在此示例中，SCOPE_IDENTITY 返回上次成功 INSERT 的标识，而不是 NULL

Note that in this example, SCOPE_IDENTITY returns the identity from the last successful INSERT, not NULL

BEGIN INSERT INTO OrderingTest(Col0,Col1,Col2) VALUES (1,2,3); INSERT INTO OrderingTest(Col0,Col1,Col2) VALUES (1,NULL,3); SELECT SCOPE_IDENTITY(); END;