CREATE TABLE [dbo].[Ingredients] (
[Ingredient_ID]                INT      IDENTITY (1, 1) NOT NULL,
[Ingredient_Description]       NVARCHAR (100) NULL);

CREATE TABLE [dbo].[Recipes] (
[Row_ID]                  INT        IDENTITY (1, 1) NOT NULL,
[Produced_Ingredient_ID]  INT        DEFAULT ((0)) NOT NULL,
[Needed_Ingredient_ID]    INT        DEFAULT ((0)) NOT NULL,
[Needed_Quantity]         FLOAT (53) DEFAULT ((0)) NOT NULL);

SELECT * FROM Recipes R INNER JOIN Ingredients I ON R.Needed_Ingredient_ID = I.Ingredient_ID WHERE R.Produced_Ingredient_ID = 5 

DECLARE @Produced_Item AS INT = 5

SELECT * FROM Recipes WHERE Produced_Ingredient_ID = @Produced_Item
UNION ALL 
SELECT * FROM Recipes WHERE Produced_Ingredient_ID IN (SELECT Needed_Ingredient_ID FROM Recipes WHERE Produced_Ingredient_ID = @Produced_Item)
UNION ALL	
...

DECLARE @ProductID AS INT = 5; 

WITH    FullList ( Ingredient_ID, Row_ID, Produced_Ingredient_ID, Needed_Ingredient_ID, Needed_Quantity )
          AS ( SELECT   I.Ingredient_ID ,
                        R.Row_ID ,
                        R.Produced_Ingredient_ID ,
                        R.Needed_Ingredient_ID ,
                        R.Needed_Quantity
               FROM     Recipes R
                        INNER JOIN Ingredients I ON R.Needed_Ingredient_ID = I.Ingredient_ID
               WHERE    R.Produced_Ingredient_ID = @ProductID
               UNION ALL
               SELECT   FL.Ingredient_ID ,
                        R.Row_ID ,
                        R.Produced_Ingredient_ID ,
                        R.Needed_Ingredient_ID ,
                        R.Needed_Quantity
               FROM     Recipes R
                        INNER JOIN FullList FL ON R.Produced_Ingredient_ID = FL.Needed_Ingredient_ID
             )
    SELECT  *
    FROM    FullList;