title: Returning Scope_Identity from 2 insert statements simultaneously in SQL Server
body: <p>I am having problem with my stored procedure: </p>
  
  <pre><code>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
  </code></pre>
  
  <p>I want to return <code>Scope_Identity</code> from both <code>Insert</code> statements and pass the values of first insert as parameter to 2nd Insert and return the <code>Scope_Identity</code> of 2nd Insert statement also.</p>
  
  <p>I am getting error is when I get the identity of first <code>Insert</code>, 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.</p>