Recursive SQL Stored Procedure

you are actually quite close...just make the CTE "recursive" with a UNION:http://msdn.microsoft.com/en-us/library/ms186243.aspx

How do I do that? and how to I pass in the id of the item I want to work up from?

did you check out the link I posted?
once you learned how to do it, you will remember it very easily ...

the trick is to "UNION" within the WITH ....

the first select in that union gives the "starting point", you have to give the condition which rows to check first.
the second select in that union joins to the CTE itself, so that has to return records based on the "parent records" returned by the first query ... and that does the recursive stuff.

if really you don't get it, post the query you get, and I will then fix it up for you

This gives me just the value for the id I supplied:

WITH Breadcrumb AS (
                                    SELECT id, parentid, Description
                                    FROM RadMenu
                               UNION ALL
                                    SELECT id, parentid, Description
                                    FROM RadMenu AS RadMenu_1)
                     -- WHERE        (parentid IS NOT NULL))
                                    SELECT id, parentid, Description
                                    FROM Breadcrumb AS Breadcrumb_1
                                    WHERE (id = @id)
                                    ORDER BY parentid

id, parentid, Description
806      650      APRO Hermes Series R:105MB/s & W:100MB/s
806      650      APRO Hermes Series R:105MB/s & W:100MB/s

While this

WITH Breadcrumb AS (
                                    SELECT id, parentid, Description
                                    FROM RadMenu
                                    WHERE (id = @id)
                              UNION ALL
                                    SELECT id, parentid, Description
                                    FROM RadMenu AS RadMenu_1)
                                    SELECT id, parentid, Description
                                    FROM Breadcrumb AS Breadcrumb_1
                                    ORDER BY parentid

Gives over 400 records, and it looks like it's returning all the rows in the database.

I can't see how to join it so it returns just the hierarchy of the id selected.

Mark

here we go:

WITH Breadcrumb AS (
   SELECT id, parentid, Description 
     FROM RadMenu
    WHERE id = @id
  UNION ALL
  SELECT r.id, r.parentid, r.Description 
    FROM RadMenu r
    -- here starts the "trick"
    JOIN Breadcrumb b 
      ON b.id = r.parentid
)
 SELECT id, parentid, Description
   FROM Breadcrumb 

Select allOpen in new window

Thanks for that unfortunatly it just returns one row, I pass in an id of 806 and get


id, parentid, description
806      650      APRO Hermes Series R:105MB/s & W:100MB/s

rather than

id, parentid, description
806      650      APRO Hermes Series R:105MB/s & W:100MB/s
650      648      Standard Temp (0°C ~ 70°C)
648      646      Industrial Grade (SLC)
646      NULL      CFast (1.0" SSD)

Where id 646 is the root record

Still not quite sure how this works BUT will try and use this as an example for any further requirements