CODEWITHSUNDEEP
sql-serverstored-procedures
naeemmalik
naeemmalik

Reputation: 77

Returning Scope_Identity from 2 insert statements simultaneously in SQL Server

I am having problem with my stored procedure: 

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

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.

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.

Upvotes: 3

Views: 3324

Answers (2)

SQL Police
SQL Police

Reputation: 4196

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 ...

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.

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

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/

Upvotes: 3

kord
kord

Reputation: 121

You need use OUTPUT clause at the procedure parameter

@ProjectId as bigint = 0 output,

Upvotes: 0

Related Questions