重置 SCOPE_IDENTITY()

Question

我有一个存储过程，它首先将一些数据插入到临时表中，然后将一行插入到另一个表中。我在第二次插入后调用 Scope_Identity() 以获取新插入的记录 Identity。

如果第二次插入由于连接而没有执行任何操作，我想检查 Scope_Identity 并引发异常。但 Scope_Identity 返回在第二次插入之前从临时表插入创建的最后一个标识。

有没有办法在调用第二次插入之前重置 SCOPE_IDENTITY，或者有更好的方法来确定第二次插入是否实际上没有插入任何内容？

Answer 1

第二次插入后立即检查@@ROWCOUNT。如果为 0，则未插入任何行。

INSERT INTO YourTable
SELECT ...

IF (@@ROWCOUNT = 0)
BEGIN
RAISERROR('Nothing inserted',16,1)
RETURN
END

Answer 2

马丁·史密斯的回答完全回答了您的问题。

这显然是互联网上唯一询问如何重置 Scope_Identity() 的页面。
我相信这对于任何使用 T-SQL 的人来说都是至关重要的。

我将这个答案留给任何来这里的人（像我一样）寻找由上一个插入语句插入的身份（并且不是最后一个随机成功的身份插入）。

这就是我想出的：

SET @SomeID = (CASE WHEN @@ROWCOUNT > 0 THEN SCOPE_IDENTITY() ELSE NULL END)

Answer 3

我认为给出的其他答案可能更实用，但我确实想在这里记录我的发现，以防有一天它对某人有所帮助。 （这是在 SQL Server 2005 中；不确定这种行为在较新的版本中是否仍然存在。）

该技巧的基础是利用以下属性（来自 @@IDENTITY 的联机丛书文档）：“ INSERT、SELECT INTO 或批量复制语句完成后，如果该语句没有影响任何具有标识列的表，则 @@IDENTITY 返回 NULL。”尽管我找不到明确的说明，但这种行为似乎也适用于 SCOPE_IDENTITY() 。因此，我们完成了一个 INSERT 语句，该语句不会影响任何具有标识列的表：

CREATE TABLE NoIdentity (notId BIT NOT NULL)

-- An insert that actually inserts sets SCOPE_IDENTITY():
INSERT INTO YourTable (name)
SELECT 'a'
WHERE 1 = 1 -- simulate a join that yields rows

SELECT @@identity, SCOPE_IDENTITY()
-- 14, 14 (or similar)

-- The problem: an insert that doesn't insert any rows leaves SCOPE_IDENTITY() alone.
INSERT INTO YourTable (name)
SELECT 'a'
WHERE 1 = 0 -- simulate a join that yields no rows

SELECT @@identity, SCOPE_IDENTITY()
-- Still 14, 14 . . . how do we know we didn't insert any rows?

-- Now for the trick:
INSERT INTO NoIdentity (notId)
SELECT 0
WHERE 1 = 0 -- we don't actually need to insert any rows for this to work

SELECT @@identity, SCOPE_IDENTITY()
-- NULL, NULL.  Magic!

INSERT INTO YourTable (name)
SELECT 'a'
WHERE 1 = 0 -- simulate a join that yields no rows

SELECT @@identity, SCOPE_IDENTITY()
-- Still NULL, NULL since we didn't insert anything.  But if we had, it would be non-NULL.
-- We can tell the difference!

因此，对于您的情况，您似乎可以

INSERT INTO NoIdentity (notId)
SELECT 0
WHERE 1 = 0

在之前重置 SCOPE_IDENTITY()执行第二个INSERT。

Answer 4

在考虑了几种替代方案后，我发现自己喜欢@BenThul对相关问题的回答：

DECLARE @result TABLE (id INT NOT NULL)

INSERT INTO YourTable (name)
OUTPUT INSERTED.id INTO @result (id)
SELECT 'a'
WHERE 1 = 0 -- simulate a join result

SELECT CASE
    WHEN (SELECT COUNT(1) FROM @result) = 1 THEN (SELECT TOP 1 id FROM @result)
    ELSE -1
END

正如你可以从我的文章中看到的最后的SELECT CASE...，在我的情况下，我试图以一个INT NOT NULL结束，这将帮助我了解是否插入了一行（在这种情况下）我想要它的 ID）或不想要。 （如果可能的话，我一开始就不建议处于这种情况！）您将使用 @result 做什么取决于您需要做什么。

我喜欢 INSERT 和 @result 之间的关系是明确的，并且不太可能被我可能没有考虑到的其他干预操作所污染。我还喜欢 @result 自然地处理插入多行的情况。

Answer 5

MikeTeeVee的答案是我与马丁-史密斯的回答非常有力。

这是我的合并使用：

BEGIN TRY

INSERT INTO YourTable
SELECT ...

SELECT @SomeID = (CASE WHEN @@ROWCOUNT > 0 THEN SCOPE_IDENTITY() ELSE NULL END)

IF (@SomeID IS NULL)
BEGIN
    RAISERROR('Nothing inserted',16,1)
END

END TRY 

BEGIN CATCH
    /* Handle stuff here - In my case I had several inserts
       - some could not happen and I did not raise errors for them
       - Some had to make a Transaction to rollback 
    */
END CATCH