SQLTeam.com | Weblogs | Forums

Update column based on the stored procedure output result

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
temp1

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:
temp

Any ideas how to do the above update ?

Thanks,
grkanth81

do this first and then use the temporary table to update your TABLE ..