在 SQL Server 中同时从 2 个插入语句返回 Scope_Identity [英] Returning Scope_Identity from 2 insert statements simultaneously in SQL Server

问题描述

我的存储过程有问题:

CREATE PROCEDURE [dbo].[Project] 
    @Code as nvarChar(255) = null,
    @Id as nvarChar(255) = null,
    @Status as nvarChar(max) = null,
    @Project as nvarChar(max) = null,
    @ClientSystem as nvarchar(max) = null,
    @UserId as bigint = 0,
    @ProjectId as bigint = 0,
    @ProjectDetailsId bigint = 0 Output
AS
    SET NOCOUNT OFF;

    IF NOT EXISTS (SELECT [Code]
                   FROM [dbo].[Project]
                   WHERE Project.Code = @Code)
    BEGIN
        INSERT INTO [dbo].[Project]([Code], [Id], [Status], [Project])
        VALUES(@Code, @Id, @Status, @Project)

        SELECT @ProjectId = SCOPE_IDENTITY()

        INSERT INTO [dbo].[ProjectDetails]([FK_ProjectId], [ClientSystem], [UserId])
        VALUES(@ProjectId, @ClientSystem, @UserId)

        SELECT @ProjectDetailsId = SCOPE_IDENTITY()
     END
     ELSE
     BEGIN
        SELECT [ProjectId] AS 'ProjectId'
        FROM [dbo].[Project]
        WHERE Project.Code = @Code
     END

我想从两个 Insert 语句返回 Scope_Identity 并将第一个 insert 的值作为参数传递给第二个 Insert 并返回 Scope_Identity第二个插入语句也是.

I want to return Scope_Identity from both Insert statements and pass the values of first insert as parameter to 2nd Insert and return the Scope_Identity of 2nd Insert statement also.

我得到的错误是当我获得第一个 Insert 的身份时，特定表中的身份增加了 2 倍，就像在 db 表中一样，它将被插入 2 但在编码中它会返回 1.当我传递给其他插入时返回它会产生冲突.

I am getting error is when I get the identity of first Insert, the identity in the specific table increases 2 times like in db table it will be inserted 2 but in coding it will return 1. And that return when i pass to other insert it s giving conflict.

推荐答案

解决方案: 不使用SCOPE IDENTITY()，而需要使用他INSERT 语句的 >OUTPUT 子句，如下所示:

Solution: Instead of using SCOPE IDENTITY(), you need to make use of he OUTPUTclause of the INSERT statement, like this:

INSERT INTO [dbo].[Project]([Code], [Id], [Status], [Project])
OUTPUT inserted.ID into @ProjectID
SELECT ...

说明: SCOPE_IDENTITY() 返回最后一个 insert 的值，无论插入发生在哪里.因此，当另一个 insert 在 parallel 中运行时，您对 SCOPE_IDENTITY() 的调用将从 other parallel 返回值运行程序.这会导致错误.

Explanation: SCOPE_IDENTITY() returns the value of the last insert, regardless where the insert takes place. So, when when another insert is running in parallel, then your call to SCOPE_IDENTITY() will return the value from the other parallel running procedure. This then leads to an error.

但是，OUTPUT 子句的使用将保证从当前INSERT返回值.

However, the usage of the OUTPUT clause will guarantee to return the value from the current INSERT.

这是一篇关于 SCOPE_IDENTITY 和并行计划的有趣文章:http://blog.sqlauthority.com/2009/03/24/sql-server-2008-scope_identity-bug-with-multi-processor-parallel-plan-and-solution/

Here is an interesting article regarding SCOPE_IDENTITY and parallel plans: http://blog.sqlauthority.com/2009/03/24/sql-server-2008-scope_identity-bug-with-multi-processor-parallel-plan-and-solution/

这篇关于在 SQL Server 中同时从 2 个插入语句返回 Scope_Identity的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！