SQL function call

Question

I am trying to call the function calculateBoxPrice() that is meant to calculate the price for the box table based on several factors. The function takes no arguments, but I need the column to be updated for every row.

This is the function:

CREATE FUNCTION calculatePrice ()
RETURNS int 
AS
BEGIN
    DECLARE @price int

    SELECT @price = m.price + s.price + t.price
    FROM project.boxes AS b
    LEFT JOIN project.types AS t ON t.type = b.type
    LEFT JOIN project.materials AS m ON m.material = b.material
    LEFT JOIN project.sizes AS s ON s.size = b.size

    RETURN @price
END

And when I call it:

UPDATE project.boxes
SET Price = dbo.calculateBoxPrice(); 

question from:https://stackoverflow.com/questions/65599729/sql-function-call

Answer 1

In SQL Server you can update with a join directly. Most other RDBMSs are similar:

update b
set price = m.price + s.price + t.price
from project.boxes as b
left join project.types as t on t.type=b.type
left join project.materials as m on m.material=b.material
left join project.sizes as s on s.size=b.size

---

In answer to your sort-of question on functions, you need to add a parameter @type and pass through b.type:

UPDATE project.boxes

SET Price = dbo.calculateBoxPrice(boxes.type)

---

Note:

The code you have given will update all rows, even when there is no matching type, material, size. You may want some combination of left join and isnull/ifnull