2

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

Mech0z
  • 3,627
  • 6
  • 49
  • 85

1 Answers1

3

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)
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • Good answer, I like common table expressions. Vote Up. – TarasB Dec 05 '10 at 18:26
  • Well from what I can see, your example wont give me sub compoenents of the sub components What about this http://csharp.pastebin.com/0wUUTFYL – Mech0z Dec 05 '10 at 20:19
  • @Mech0z - Yes it will. See my edit for an example. It will carry on doing `UNION ALL` s on the result of the last `UNION ALL` until no more results are returned. I realise that isn't phrased very well! Better explanation [here](http://stackoverflow.com/questions/3187850/how-does-a-recursive-cte-run-line-by-line/3187907#3187907) – Martin Smith Dec 05 '10 at 20:44
  • How do I rewrite it to a Procedure then? And is this correct then http://csharp.pastebin.com/zmiVTtnQ (apart from it being a function instead of Procedure – Mech0z Dec 05 '10 at 20:51
  • Just call the TVF from your procedure. – Martin Smith Dec 05 '10 at 20:51