CTE Function

CTE fucntion can be used on this way:

IF OBJECT_ID('tempdb..#dataList') IS NOT NULL
DROP TABLE #dataList


CREATE TABLE #dataList(
id            INT
,name        VARCHAR(50)
,parent        INT
,ip            INT
)


INSERT #dataList
SELECT 1    id, 'Group1'        Name, 0 Parent, 0 ip UNION ALL
SELECT 2    id, 'SubGroup1'        Name, 1 Parent, 0 ip UNION ALL
SELECT 3    id, 'Item1'            Name, 2 Parent, 1 ip UNION ALL
SELECT 4    id, 'Item2'            Name, 2 Parent, 1 ip UNION ALL
SELECT 5    id, 'Item3'            Name, 2 Parent, 1 ip UNION ALL
SELECT 6    id, 'Item4'            Name, 2 Parent, 1 ip UNION ALL
SELECT 7    id, 'SubGroup2'        Name, 1 Parent, 0 ip UNION ALL
SELECT 8    id, 'Item5'            Name, 7 Parent, 1 ip UNION ALL
SELECT 9    id, 'Item6'            Name, 7 Parent, 1 ip UNION ALL
SELECT 10    id, 'Item7'            Name, 7 Parent, 1 ip UNION ALL
SELECT 11    id, 'Item8'            Name, 7 Parent, 1 ip


--SELECT * FROM #dataList



DECLARE @id INT = 1
;WITH CTE(id, name, parent, ip)
AS
(
SELECT * FROM #dataList WHERE id = @id
UNION ALL
SELECT d.* FROM #dataList D
INNER JOIN CTE c ON d.parent = c.id
)

SELECT * FROM CTE WHERE ip = 1 ORDER BY id ASC;