Как написать функции?
Используем CTE:

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
Во-первых у вас 4 уровня, а не 3.
Во-вторых, конечно, нужна рекурсия.
Но по колхозному тоже работает:DECLARE @S FLOAT, @ORD INT = 1
SELECT @S = ISNULL(SUM(price),0) FROM OrderItems WHERE (order_id = @ORD)
SET @S = @S + 
  ( SELECT ISNULL(SUM(OrderItems.price), 0) 
    FROM OrderItems 
      INNER JOIN Orders AS P1 ON OrderItems.order_id = P1.row_id
      WHERE (P1.parent_id = @ORD))
SET @S = @S + 
  ( SELECT ISNULL(SUM(OrderItems.price), 0) 
    FROM OrderItems 
      INNER JOIN Orders AS P1 ON OrderItems.order_id = P1.row_id
      INNER JOIN Orders AS P2 ON P1.parent_id = P2.row_id
      WHERE (P2.parent_id = @ORD))
SET @S = @S + 
  ( SELECT ISNULL(SUM(OrderItems.price), 0) 
    FROM OrderItems 
      INNER JOIN Orders AS P1 ON OrderItems.order_id = P1.row_id
      INNER JOIN Orders AS P2 ON P1.parent_id = P2.row_id
      INNER JOIN Orders AS P3 ON P2.parent_id = P3.row_id
      WHERE (P3.parent_id = @ORD))
SELECT @S
В функцию переделаете сами.