Sql get all children in table
I have a table called tblmodules that has 3 columns: moduleid, name, parent_id. The parent_id column takes on the values of other modules. eg:
Moduleid Name Parentid
-------- ----- --------
1 grandparent Null
2 parent 1
3 child 2
4 childofChild 3
I want to build a stored procedure to get all children if I pass the stored procedure as a parameter to grandparent. for the above example, I don't want only (parent), but I want the child and the child to be children because they are under the grandfather. Any help pls?
+3
source to share
1 answer
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
+3
source to share