How to write functions?

Question

SQL Server

database schema

in the Orders table, the data is stored in a hierarchical order, parent_id contains the row_id of the ancestor. There are three levels in the hierarchy.
We need to write two functions:
1) Write the select_orders_by_item_name function. It takes one argument - the item name (string),
and must find all orders that have an item with the given name. In addition, she must
count the number of items with the specified name in each individual order. The result of the function call
should be a table:

CREATE FUNCTION select_orders_by_item_name (@string nvarchar(max))
returns Table
AS  
RETURN   
(  
    select distinct Orders.row_id,Customers.name from orders,orderitems,Customers 
  where [email protected]string and OrderItems.order_id=Orders.row_id
  and Customers.row_id=Orders.customer_id  
);

She finds orders, but does not count them.

2) Write a function calculate_total_price_for_orders_group. It receives the row_id of a group (or order),
and returns the total cost of all positions of all orders in this group (order), and the
summation must be performed over the entire subtree of orders starting from this group. The function must return a number.

CREATE FUNCTION calculate_total_price_for_orders_group(@f int)
RETURNS FLOAT
AS
BEGIN
  DECLARE @s int 
 SET @s=(SELECT Sum(OrderItems.price)
      FROM orderitems INNER JOIN orders
      ON orders.row_id = OrderItems.order_id
      GROUP BY Orders.row_id
      HAVING [email protected])
  RETURN (@s)
END

It counts but does not take into account the hierarchical structure of the table, that is, it normally counts only for those rows that are themselves orders, and not groups of orders. I realized that I need to make a view that will contain all nested orders, in the event that the index is entered not of an order, but of a group of orders.

Answer 1

We use CTEs:

with cte_orders(row_id, parent_id, group_name) as (
  select row_id, parent_id, group_name from Orders where row_id = @f
  	union all
  	select O.row_id, O.parent_id, O.group_name 
  	from Orders O 
  	join cte_orders on cte_orders.row_id = O.parent_id
) 
select sum(price) total_pice from cte_orders 
left join OrderItems on cte_orders.row_id = OrderItems.order_id ;

T-SQL fiddle