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

Question

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

As per the question guide, please DO NOT post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. — Dale K
– Dale K, Commented Apr 12, 2021 at 20:40
"...an ID that I need to return and store in a variable in Azure Data Factory" - you've already answered your own question. You need to store the ID and pass it with the values to update. As you don't provide any information about this part of your system there is not much assistance we can provide. I should also point out that the return value of an SP is not intended for passing data, its intended for passing the status of the SP run. You should be using an output parameter. — Dale K
– Dale K, Commented Apr 12, 2021 at 20:43
Side note: you should not use the sp_ prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoid sp_ and use something else as a prefix - or no prefix at all! — marc_s
– marc_s, Commented Apr 12, 2021 at 20:56
@DaleK I have updated my post with text and added the table schema as well. I am new to SQL Server and also to writing Stored Procedures so your help is very much appreciated if you can have a look at my table schema and provide any further feedback. — Mariya
– Mariya, Commented Apr 12, 2021 at 21:12

Stu · Accepted Answer · 2021-04-12 20:01:41Z

1

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.

answered Apr 12, 2021 at 20:01

Stu

32.7k6 gold badges17 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Mariya Over a year ago

Thanks @Stu for your feedback. I made the changes as per your suggestion but getting an error that stored procedure didn't return any value. I have added my table schema if you can look at it again. Thanks again for your help

Stu Over a year ago

2 questions - how are you capturing the return value when you run your first procedure, and on your table schema presumably SF_Integration_Audit_ID is the unique key which is an identity

Mariya Over a year ago

Yes SF_Integration_Audit_ID is the unique auto generated field which is an identity. I am using the following code to capture the return value, not sure if it is the correct way SET SF_Integration_Audit_ID = SCOPE_IDENTITY() RETURN SF_Integration_Audit_ID. The validation check is not allowing me to append at sign infront of the parameter

Stu Over a year ago

Yes you're doing that right. Your procedure will return that value. I don't know how you are calling this first procedure though. Presumably from client code in which case you will need to capture the returned value. If you were calling your first procedure from another Sql procedure or testing using SSMS you would exec @returnedvalue = SP_Insert_Into_IntegrationAudit

Mariya Over a year ago

oh that's what you meant. I am calling it in Azure Data Factory as a variable using the Set Variable activity that gets output from the Lookup activity

|

Collectives™ on Stack Overflow

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

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related