Sql get all childs in table

Question

I have a table called tblmodules which has 3 columns: moduleid,name,parent_id. The column parent_id takes values of other modules. ex:

Moduleid      Name       Parentid
--------     -----       --------
1           grandparent    Null
2            parent         1
3           child           2
4           childofChild    3

I want to construct a stored procedure to get all the childs if i give to the stored procedure as a parameter the grandparent. for the above example i do not want only the (parent) but i want the child and the childofchild because they are under grandparent. Any help pls ?

Answer 1

You need a recursive CTE

CREATE PROC YourProc 
@ModuleId INT
AS
    WITH R
         AS (SELECT *
             FROM   YourTable
             WHERE  Moduleid = @ModuleId
             UNION ALL
             SELECT Y.*
             FROM   YourTable Y
                    JOIN R
                      ON R.Moduleid = Y.Parentid)
    SELECT *
    FROM   R 

SQL Fiddle