Nice way to select all the child elements in SQL

The task is to select all the records where parent equal ‘1’ and parent of parent equal ‘1’ etc. Our datatable is:

Let’s create a temporary table

DECLARE @tbl TABLE
(

	EntityId int,
	Name nvarchar(255),
	ParentId int
)

And insert records to it as shown above

INSERT into @tbl (EntityId, Name, ParentId) SELECT 1, 'Dep1', 0
INSERT into @tbl (EntityId, Name, ParentId) SELECT 2, 'Dep2', 1
INSERT into @tbl (EntityId, Name, ParentId) SELECT 3, 'Dep3', 1
INSERT into @tbl (EntityId, Name, ParentId) SELECT 4, 'Dep4', 1
INSERT into @tbl (EntityId, Name, ParentId) SELECT 5, 'Dep5', 2

Now let’s write an SQL-expression

DECLARE @ID Int
SELECT @ID = 1

;WITH results AS(
    	SELECT	*
    	FROM	@tbl
    	WHERE	ParentId = @ID
    	UNION ALL
    	SELECT	t.*
    	FROM	@tbl t INNER JOIN
    			ret r ON t.ParentId = r.EntityId
)

SELECT  *
FROM    results

The result is: