title: Stored procedures to insert and then update the table based on identity column
body: <p>I have a use case where I need to create 2 stored procedures (Insert data, update records) for Audit Table.</p>
  <p>First SP inserts 6 out of 12 columns of data into the table and auto generates an ID that I need to return and store in a variable in Azure Data Factory but for some reason I am not able to get that value. I get an error saying that stored procedure didn't return any value, not sure what I am missing.</p>
  <p>After this the next pipeline will run in Azure Data Factory and once the run completes, I need to take the output values from the Data Factory and update the same table with the remaining 6 records that were generated after the pipeline run.</p>
  <p>Below is my table schema</p>
  <pre><code>CREATE TABLE [gds].[TBL_SF_INTEGRATION_AUDIT](
  [SF_Integration_Audit_ID] [int] IDENTITY(5000,1) NOT NULL,
  [Integration_Batch_ID] [int] NULL,
  [Pipeline_Run_StartDT] [datetime] NULL,
  [Source_Schema] [varchar](1000) NOT NULL,
  [Source_Object] [varchar](1000) NOT NULL,
  [Target_Entity] [varchar](500) NOT NULL,
  [Target_Load] [varchar](1000) NOT NULL,
  [Source_Rows_Selected] [int] NULL,
  [Batch_Count] [int] NULL,
  [Successful_Batch_Count] [int] NULL,
  [Failed_Batch_Count] [int] NULL,
  [Successful_Inserted_Rows] [int] NULL,
  [Failed_Rows] [int] NULL,
  [Pipeline_Run_EndDT] [datetime] NULL
  </code></pre>
  <p>I have created an insert Stored Procedure and an Update Stored Procedure. I am using <code>Scope_Identity()</code> to get the latest values of the identity column but I get an error saying that stored procedure did not return any value.</p>
  <pre><code>ALTER PROCEDURE [dbo].[SP_Insert_Into_IntegrationAudit]
  (
      -- Add the parameters for the stored procedure here 
      @Integration_Batch_ID int,
      @Pipeline_Run_StartDT datetime,
      @Source_Schema varchar(1000),
      @Source_Object varchar(1000), 
      @Target_Entity varchar(500), 
      @Target_Load varchar(1000),
      @SF_Integration_Audit_ID int out
  )
  AS
  BEGIN
  
      -- Insert statements for procedure here
     INSERT INTO gds.TBL_SF_INTEGRATION_AUDIT
     (Integration_Batch_ID, Pipeline_Run_StartDT, Source_Schema, Source_Object,
     Target_Entity, Target_Load)
     VALUES (@Integration_Batch_ID, @Pipeline_Run_StartDT, @Source_Schema,
             @Source_Object, @Target_Entity, @Target_Load)
  
      SET @SF_Integration_Audit_ID = SCOPE_IDENTITY()
      RETURN @SF_Integration_Audit_ID 
  
  
  END
  </code></pre>
  <p>And for the Update SP I am not sure how can I pass the ID from the first stored procedure so that SQL knows the correct rows in which the new data should be inserted/updated? I have altered the SP for update as per suggestion.</p>
  <pre><code>ALTER PROCEDURE [dbo].[SP_Update_IntegrationAudit]
  (
      -- Add the parameters for the stored procedure here
      @Source_Rows_Selected int,
      @Batch_Count int,
      @Successful_Batch_Count int,
      @Failed_Batch_Count int,
      @Successful_Inserted_Rows int, 
      @Failed_Rows int, 
      @Pipeline_Run_EndDT datetime,
      @SF_Integration_Audit_ID int
  )
  
  AS
  BEGIN
  
      -- Insert statements for procedure here
     UPDATE gds.TBL_SF_INTEGRATION_AUDIT
     SET Source_Rows_Selected = @Source_Rows_Selected, 
      Batch_Count = @Batch_Count,
      Successful_Batch_Count = @Successful_Batch_Count,
      Failed_Batch_Count = @Failed_Batch_Count,
      Successful_Inserted_Rows = @Successful_Inserted_Rows, 
      Failed_Rows = @Failed_Rows, 
      Pipeline_Run_EndDT = @Pipeline_Run_EndDT
      WHERE SF_Integration_Audit_ID = @SF_Integration_Audit_ID
  
  END
  GO
  </code></pre>
  <p>Any help will be much appreciated! Thanks!!</p>