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

Hi,

I strongly recommend to use a scalar function or even C# external function, if needed. It is the most elegant way to solve your problem. I use both of them and have no performance issues even if they are run on not so fast VM's and execute quite complex queries.

Answer 2

Hi @Damir Padavić ,

Using Scalar function in SQL Server (especially before sql server 2019 which had improvement in this specific point) is usually a very bad idea which might imply poor design of the system. The main reason is that the Scalar Function is not executed or analyzed as part of the query that call it. SQL Server does not execute queries as we write them but instead it always create execution plan (choosing from many that he build and compare) and for best execution plan the server should analyzed the full query and not having a black box. In addition when we are using a non-inline function then the server probably had to execute the function for each row separately.

For the reason you should try to avoid using multi-statement table valued function in most cases and prefer to use inline table valued function. Unfortunately before SQL Server 2019 there was no inline scalar valued function.

In addition! Tabular databases like SQL Server are designed to work with SET of rows and for this they uses advance algorithm which are best for this task. These ability to work with SET of data usually coming at the expense of working against individual values by using loops.

In general as a golden rule, we should try to avoid using loops and we should try to avoid using scalar functions and multi-statement table valued function.

Note! As mentioned above SQL Server 2019 introduced new ability to support inline scalar function. This is a game changer in cases that this feature fits,