Save temp list in recursive Stored Procedure
I want to make a Stored procedure which is made recursive
The database has a list of parts, and a part can be "containedin" another part so this "containedin" has an ID
开发者_开发百科I then want to make a function that
It will take one parameter "ID"
Print ID
Find ID on equipment which containedin is equal to this ID
Save these IDs in a list
Call this function again with all the IDs in the list
How do I make that list
If this is SQL Server 2005+ then use an inline TVF that contains a recursive CTE. Example Below.
USE tempdb;
CREATE TABLE dbo.EquipElement(
EquipmentID int not null primary key,
ContainedIn int null references EquipElement(EquipmentID),
Description varchar(20))
INSERT INTO dbo.EquipElement
SELECT 1, NULL, 'Breakfast' UNION ALL
SELECT 2, 1, 'Fry Up' UNION ALL
SELECT 3, 1, 'Coffee' UNION ALL
SELECT 4, 2, 'Eggs' UNION ALL
SELECT 5, 2, 'Bacon' UNION ALL
SELECT 6, 2, 'Sausages' UNION ALL
SELECT 7, 3, 'Milk' UNION ALL
SELECT 8, 3, 'Sugar'
GO
CREATE FUNCTION dbo.GetBOM
(
@EquipmentID int
)
RETURNS TABLE
AS
RETURN
(
WITH cte AS
(
SELECT EquipmentID,ContainedIn,Description
FROM dbo.EquipElement
WHERE EquipmentID = @EquipmentID
UNION ALL
SELECT e.EquipmentID,e.ContainedIn,e.Description
FROM dbo.EquipElement e
JOIN cte c on e.ContainedIn = c.EquipmentID
)
SELECT EquipmentID,ContainedIn,Description
FROM cte
)
GO
SELECT * FROM dbo.GetBOM(1)
SELECT * FROM dbo.GetBOM(3)
精彩评论