nested loop function

I want to know how to create a recursive loop/function in SQL, I can't seem to figure out how to do it.

The database table I am working with is simply the following:

SeedID, ThisParentSeedID

1, 0

2, 1

3, 1

4, 2

5, 4

6, 5

7, 6

8, 7

9, 7

10, 7

11, 10

12, 0

13, 0

14, 0

The example table above shows that SeedID 1 = the parent level of the data. SeedID 2 and 3 are children of SeedID 1, 4 is child of 2, 5 is child of 4... 12 13 and 14 are also parent levels (they are not children of anything).

I want to know how to create a SQL script that is "object oriented" in that I will not have to create as many levels of nested scripts as there are nested "children" in the data.

What I am wanting to figure out is, with a single script, "which sub-children are assigned to [@.SeedID]"? So if this script was called, and @.SeedID = 1, it would return (2,3,4,5,6,7,8,9,10,11). If @.SeedID = 12, it would return null. If @.SeedID = 7, it would return (8,9,10,11)

I have tried to keep my question and data as simple as possible for the sake of getting some feedback or help. If you want me to clarify or explain better, please ask me to!

CTE (Common Table expression) is best suited for your needs. Here you go

Declare @.ID as INT
SET @.ID = 7

;WITH myCTE AS
(
SELECT SeedID, ParentID FROM Seeds where SeedID = @.ID
UNION ALL
SELECT Seeds.SeedID, Seeds.ParentID From Seeds INNER JOIN myCte
ON Seeds.ParentID = myCte.SeedID
)
SELECT SeedID FROM myCTE where SeedID <> @.ID

|||

--Create the functionCREATE FUNCTION dbo.udf_GetChildren (@.parentIdint )RETURNSVarchar(100)ASBEGINDECLARE @.Childvarchar(100)SELECT @.Child =coalesce(@.child,'') + (casewhen @.childisnot nullthen','else''end ) +convert(varchar,SeedId)FROM YourTable CWHERE C.ThisParentSeedID = @.parentIdSELECT @.Child = @.Child + dbo.udf_GetChildren(seedid)FROM YourTable CWHERE C.ThisParentSeedID = @.parentIdReturnCoalesce(@.child,'')END--Call the functionSELECT dbo.udf_GetChildren(1)