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, Apr 12 '21 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, Apr 12 '21 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*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! — marc_s, Apr 12 '21 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, Apr 12 '21 at 21:12
Your question appears to relate to the context which is calling these SP's, but you don't provide any information about that. You are also returning the new ID as both the return value and an output parameter now. Whatever calls your SP must read the output parameter, store that new id somewhere, and match it against the update values before calling the update SP. But we have no idea how all that happens. FYI a `select top` query does not tell us the schema, you need to script table create for that. — Dale K, Apr 12 '21 at 21:21

score 1 · Accepted Answer · answered Apr 12 '21 at 20:01

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 '21 at 20:01

Stu

30,392
6
14
33

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 – Mariya Apr 12 '21 at 21:08
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` – Stu Apr 12 '21 at 21:12
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 – Mariya Apr 12 '21 at 21:20
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` – Stu Apr 12 '21 at 21:24
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 – Mariya Apr 12 '21 at 21:30
I don't use ADF unfortunately so can only advise on the Sql Server aspect. If you are having problems calling a sql procedure and capturing its return value suggest that's worthy of its own question. – Stu Apr 12 '21 at 21:31
No worries Stu. Thanks for your help!! – Mariya Apr 12 '21 at 23:16

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

1 Answers1