SQL Server 2016

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- auto-generated definition
create table articles
(
    code           int not null
        constraint PK_articles
            primary key,
    name           varchar(254),
)


create index code
    on articles (code)


INSERT INTO articles (code, name) VALUES (2, N'Bottle 2L (Dirty)');
INSERT INTO articles (code, name) VALUES (4, N'Bottle 2L (Washed)');
INSERT INTO articles (code, name) VALUES (6, N'Red Lid');
INSERT INTO articles (code, name) VALUES (9, N'Carbonated Water 2L Etiquette');
INSERT INTO articles (code, name) VALUES (10, N'Carbonated water');

-- auto-generated definition
create table recipes
(
    code   int
        constraint recipes_pk
            primary key,
    article int           not null
)

INSERT INTO recipes (code, article) VALUES (38, 3);
INSERT INTO recipes (code, article) VALUES (39, 4);
INSERT INTO recipes (code, article) VALUES (40, 10);
INSERT INTO recipes (code, article) VALUES (41, 11);

-- auto-generated definition
create table articles_in_recipe
(
    code   int,
    recipe   int            not null,
    article int            not null,
    quantity decimal(15, 3) not null
)
  
INSERT INTO articles_in_recipe (code, recipe, article, quantity) VALUES (1, 38, 1, 1.000);
INSERT INTO articles_in_recipe (code, recipe, article, quantity) VALUES (137, 41, 8, 1.000);
INSERT INTO articles_in_recipe (code, recipe, article, quantity) VALUES (138, 41, 7, 1.000);
INSERT INTO articles_in_recipe (code, recipe, article, quantity) VALUES (139, 41, 4, 1.000);
INSERT INTO articles_in_recipe (code, recipe, article, quantity) VALUES (145, 40, 9, 1.000);
INSERT INTO articles_in_recipe (code, recipe, article, quantity) VALUES (146, 40, 6, 6.000);
INSERT INTO articles_in_recipe (code, recipe, article, quantity) VALUES (147, 40, 4, 1.000);
INSERT INTO articles_in_recipe (code, recipe, article, quantity) VALUES (149, 39, 2, 6.000);
INSERT INTO articles_in_recipe (code, recipe, article, quantity) VALUES (150, 39, 6, 1.000);

-- auto-generated definition
create table dbo.stock_movements
(
    code             int,
    article           int,
    quantity         DECIMAL(15,3),
)

INSERT INTO stock_movements (code, article, quantity) VALUES (7763977, 6, 5.000);
INSERT INTO stock_movements (code, article, quantity) VALUES (7763978, 7, 5.000);
INSERT INTO stock_movements (code, article, quantity) VALUES (7763979, 2, 5.000);
INSERT INTO stock_movements (code, article, quantity) VALUES (7763981, 4, 29.000);
22 rows affected

CREATE FUNCTION [dbo].[Calculate_Stock]
(
	@Code int
)
RETURNS NUMERIC
AS
BEGIN
	DECLARE @return NUMERIC(15,3);

	SELECT @return = ISNULL(SUM(quantity), 0)
	FROM stock_movements
	WHERE article = @Code;

	RETURN @return;
END;
CREATE PROCEDURE calculate_recipe
(
    @recipe INT,
    @quantity DECIMAL(15, 3)
)
AS
BEGIN
    SET NOCOUNT ON;
    
    -- Recursive CTE to calculate required recipes and quantities
    WITH cte AS (
        -- Anchor member retrieves the initial recipe details
        SELECT
            ar.recipe AS recipe,
            ar.article AS article,
            CAST(ar.quantity * @quantity AS DECIMAL(15, 3)) AS total_quantity,
            CASE
                WHEN CAST(ar.quantity * @quantity AS DECIMAL(15, 3)) <= dbo.Calculate_Stock(ar.article) THEN 0
                ELSE CAST(ar.quantity * @quantity - dbo.Calculate_Stock(ar.article) AS DECIMAL(15, 3))
            END AS remaining_quantity,
            1 AS RecursionLevel
        FROM
            recipes r
            INNER JOIN articles_in_recipe ar ON r.code = ar.recipe
        WHERE
            r.code = @recipe
        UNION ALL
        -- Recursive member to calculate required recipes recursively
        SELECT
            ar.recipe AS recipe,
            ar.article AS article,
            CAST(ar.quantity * @quantity AS DECIMAL(15, 3)) AS total_quantity,
            CASE
                WHEN cte.remaining_quantity * ar.quantity <= dbo.Calculate_Stock(ar.article) THEN 0
                ELSE CAST((cte.remaining_quantity * ar.quantity - dbo.Calculate_Stock(ar.article)) AS DECIMAL(15, 3))
            END AS remaining_quantity,
            cte.RecursionLevel + 1
        FROM
            cte
            INNER JOIN recipes r ON cte.article = r.article
            INNER JOIN articles_in_recipe ar ON r.code = ar.recipe
        WHERE
            cte.RecursionLevel < 10 -- Adjust the recursion limit as needed,
            AND cte.remaining_quantity > 0
    )
    -- Insert the required articles and quantities into a temporary table
    SELECT
        recipe,
        article,
        SUM(total_quantity) AS total,
        SUM(remaining_quantity) AS remaining
    INTO #RequiredArticles
    FROM cte
    GROUP BY article, recipe;

    -- Retrieve the final list of required articles and their quantities
    SELECT
        ra.article,
        ra.recipe,
        a.name AS ArticleName,
        ra.total,
        dbo.Calculate_Stock(ra.article) AS stock,
        ra.remaining
    FROM
        #RequiredArticles ra
        INNER JOIN articles a ON ra.article = a.code;

    -- Drop the temporary table
    DROP TABLE #RequiredArticles;

END;
EXEC calculate_recipe @recipe = 40, @quantity = 30


article
recipe
ArticleName
total
stock
remaining


2
39
Bottle 2L (Dirty)
180.000
5
1.000

6
39
Red Lid
30.000
5
0.000

4
40
Bottle 2L (Washed)
30.000
29
1.000

6
40
Red Lid
180.000
5
175.000

9
40
Carbonated Water 2L Etiquette
30.000
0
30.000
article	recipe	ArticleName	total	stock	remaining
2	39	Bottle 2L (Dirty)	180.000	5	1.000
6	39	Red Lid	30.000	5	0.000
4	40	Bottle 2L (Washed)	30.000	29	1.000
6	40	Red Lid	180.000	5	175.000
9	40	Carbonated Water 2L Etiquette	30.000	0	30.000