SQL Server unlimited multi-level classification query Solution

In development, users often encounter the problem of unlimited classification. The most common problems are some infinitely level directories. If you are not familiar with SQL Server or are not familiar with the SQL language, this will be a very difficult task. Maybe you can implement similar functions in the end, but it may take a long time and the efficiency is not ideal, the following example may give you some inspiration.

/***** Create a table ****/
Create Table [DBO]. [tree] (
[ID] [int] identity (1, 1) not null,
[Pid] [int] Null,
[Name] [varchar] (10) Collate chinese_prc_ci_as null
) On [primary]

/***** Create a UDF ****/
Create Function DBO. fgettreetable
(
@ ID Int = NULL
)
Returns @ tab table (ID int, PID int, name varchar (10), levint) -- table structure of the returned results
As
Begin
Declare @ levint
Set @ lev= 0

While @ lev= 0 or @ rowcount> 0
Begin
Set @ lev= @ lev+ 1
Insert @ tab (ID, PID, name, column)
Select ID, PID, name, @ levfrom tree where (@ lev= 1 and (pid = @ ID) or (@ ID is null and PID is null ))) or (PID in (select ID from @ tab where lev= @ Lev-1 ))
Order by ID
End
Return
End

You can directly query select * From DBO. fgettreetable (null) to enter all records.

Note: This article is reproduced from: http://drc.cnblogs.com/articles/269062.html. if you have any questions, you can submit them to the original author.