Как можно рекурсию добавить в функцию?

Question

[Feroman1]

У меня есть рекурсия:

WITH calculate_cte(row_id, parent_id, group_name) AS (
SELECT row_id, parent_id, group_name FROM Orders WHERE row_id = 1(Вот сюда хочу свое значение)
UNION ALL
SELECT Orders.row_id, Orders.parent_id, Orders.group_name
FROM Orders
JOIN calculate_cte ON calculate_cte.row_id = Orders.parent_id
)
SELECT SUM(price) AS total_price from calculate_cte
left join OrderItems ON calculate_cte.row_id = OrderItems.order_id ;

Как создать функцию с with?

Comments

[Константин Цветков]

Покажите скрипт создания функции и текст (не картинку) ошибки.

Answer 1

[Константин Цветков]

DROP TABLE IF EXISTS Orders
DROP TABLE IF EXISTS OrderItems
GO
CREATE TABLE Orders ( row_id INT, parent_id  INT, group_name VARCHAR(50) )
CREATE TABLE OrderItems ( order_id INT, price MONEY )
GO
INSERT Orders VALUES (1, NULL, 'test1'),
(2, NULL, 'test2'),
(3, 1, 'test1-3' ),
(4, 3, 'test1-3-4'),
(7, 3, 'test1-3-7'),
(5, 2, 'test2-5' ),
(6, 2, 'test2-6' ),
(8, 2, 'test2-8' ),
(9, 7, 'test1-3-7-9' )
GO
INSERT OrderItems VALUES (1, 11),
(2, 12),
(3, 13),
(4, 14),
(7, 15),
(5, 16),
(6, 17),
(8, 18),
(9, 19)
GO

CREATE OR ALTER FUNCTION dbo.OrderSum (@ORD INT) RETURNS MONEY
AS BEGIN
  DECLARE @SUMM MONEY;
  WITH calculate_cte(row_id, parent_id, group_name) AS (
    SELECT row_id, parent_id, group_name FROM Orders WHERE row_id = @ORD
    UNION ALL
    SELECT Orders.row_id, Orders.parent_id, Orders.group_name
      FROM Orders
        INNER JOIN calculate_cte ON calculate_cte.row_id = Orders.parent_id
    )
  SELECT @SUMM = SUM(price) FROM calculate_cte
    INNER JOIN OrderItems ON calculate_cte.row_id = OrderItems.order_id
  RETURN @SUMM
END
GO

SELECT dbo.OrderSum(1), dbo.OrderSum(2), dbo.OrderSum(3), dbo.OrderSum(4)

Comments

[Feroman1]

Спасибо