插入失败后如何继续执行当前块? [英] How can I continue execution of the current block after an INSERT failure?

问题描述

考虑下表:

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; 

这篇关于插入失败后如何继续执行当前块?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！