CODEWITHSUNDEEP
sql-serverstored-proceduresssmssqlparameterazure-data-factory
Mariya
Mariya

Reputation: 65

Stored procedures to insert and then update the table based on identity column

I have a use case where I need to create 2 stored procedures (Insert data, update records) for Audit Table.

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.

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.

Below is my table schema

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

I have created an insert Stored Procedure and an Update Stored Procedure. I am using Scope_Identity() to get the latest values of the identity column but I get an error saying that stored procedure did not return any value.

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

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.

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

Any help will be much appreciated! Thanks!!

Upvotes: 0

Views: 1972

Answers (1)

Stu
Stu

Reputation: 32614

First, absolutely yes, scope_identity is the right way to get the Id value.

You say you are storing the returned Id value from your first procedure call (I presume the column is called Id you don't provide the table schema), so you can include a parameter on your second procedure for it (@Id int) and just pass it along with the other values you are already supplying - your comment suggests you are going to do that?

Then in your second procedure you simply

update gds.TBL_SF_INTEGRATION_AUDIT set
<columns=@values>
where id=@id

Another way of capturing the Identity is to use the output clause to capture the identity value along with other correlating values into a separate table.

You can do the following

insert into gds.TBL_SF_INTEGRATION_AUDIT (<columns>) 
output inserted.Id, inserted.Integration_Batch_Id into <another table> -- or any other columns needed
values (<values>)

You can then join to this using the known values to find the Id that was generated.

Upvotes: 1

Related Questions