Update column based on the stored procedure output result

Question

Hi all,

I would like to update a table column based on the stored procedure output result and this stored procedure will be taking parameter values as below table column values. Is this possible ?
Here is the situation:
Declare @temp table
(workdate datetime,
employeeid int,
fileid int,
category varchar(2),
rate money
)
insert @temp
Select '2020-08-01',20,1230,'PE',null
union
Select '2020-08-04',40,5210,'TY',null
union
Select '2020-08-12',80,7530,'RD',null
union
Select '2020-08-28',90,2250,'KF',null

Select * from @temp

So, I would like to update the rate column in the above table based on a stored procedure usp_GetPFDetails output result. I initially thought i can get the sql code from this stored procedure and write a simple update statement accordingly. But I noticed that this stored procedure is a complex one and uses 4 nested stored procedures (i.e stored procedure inside another stored procedure inside another stored procedure) etc.
This stored procedure accepts the above table column values as parameters i.e when you execute the stored procedure 4 times with the above values you will get the column rate values:
exec usp_GetPFDetails '2020-08-01',20,1230,'PE' -- when this is executed i get 825.00
exec usp_GetPFDetails '2020-08-04',40,5210,'TY' -- when this is executed i get 523.00
exec usp_GetPFDetails '2020-08-12',80,7530,'RD' -- when this is executed i get 1045.00
exec usp_GetPFDetails '2020-08-28',90,2250,'KF' -- when this is executed i get 620.00

The rate column in the @temp table should get updated with the above above stored procedure results as below:

Any ideas how to do the above update ?

Thanks,
grkanth81

Answer 1

Still not clear to me

If all you need is to returns a SET of data for a report, then I am not sure that you need any temporarily table or your SP. We need to see the real scenario in order to choose if a temp table and the SP are needed. Your assumption is that you need these and you search for solution on how to use it. My feeling is that the SP is not needed and you simply need to build a query which return the information that you need for your report directly.

Anyway, you should probably not execute the SP multiple times for each row in a table in order to update each row separately.

If you provide real information to reproduce the scenario the we did not need so many stories and attempt to explain what we could see.

So I would like to know how to update that rate column in @temp table from that stored procedure output for all the records in the @temp table result.

This is my point!!!

I get a feeling that you ask the wrong question and you do not need to do this task

You ask "how to do X?" while I am asking "Do you really need to do X, when your real goal is Y" ?

We need to understand what is your real goal and what is your real source data. If the real goal is the report then why do you use that temporarily table and this SP?!? You probably need to use a different road

I think you should start from scratch and provide DDL+DML+ explain what is your goal (report mean a SELECT query which return something which is the goal for the task) and what is your expected result from the DDL+DML

Answer 2

You can use the CURSOR to update the rate one by one if you do not want to make a big change from your existing code:

DECLARE @workdate datetime;
DECLARE @employeeid int;
DECLARE @fileid int;
DECLARE @category varchar(2);
DECLARE @rate money;
DECLARE MyCursor CURSOR FORWARD_ONLY FOR
    SELECT workdate, employeeid, fileid, category
    FROM @temp;
OPEN MyCursor;
FETCH NEXT FROM MyCursor INTO @workdate, @employeeid, @fileid, @category;
WHILE @@FETCH_STATUS = 0
BEGIN
    -- Get rate from SP
    EXEC @rate = usp_GetPFDetails @workdate, @employeeid, @fileid, @category;

    -- Update rate in the table
    UPDATE @temp
    SET rate = @rate
    -- Use a unique column in the WHERE clause if it exists
    WHERE workdate = @workdate AND employeeid = @employeeid AND fileid = @fileid AND category = @category;

    FETCH NEXT FROM MyCursor INTO @workdate, @employeeid, @fileid, @category;
END
CLOSE MyCursor;
DEALLOCATE MyCursor;
GO

Answer 3

The best solution would be to rework the existing procedure so that it can work with sets of data, but having done this a few time, I know that this is not a walk in a park for a complex stored procedure.

It is not clear how this procedure returns the value, but I get the impression that it is as a result set. You can capture this with INSERT-EXEC - at least as long as the procedure is not using INSERT-EXEC itself. And then you need to loop over your table as in Guoxiong's post.

But as long as you have the power to change that procedure, I think it would be better to push the main logic of that procedure to an inner procedure which returns the rate in as an output parameter. The existing procedure would be a wrapper that retrieves the output parameter and returns it like to day.

Answer 4

Ok Thank you Pituach, EchoLiu, Erland
I will go back to the business and will get the requirements again and start from scratch.
Thank you Guoxiong for that cursor. It will be definitely useful for me.

Answer 5

ok sure Echo. I will repost in another post only.