What kind of script or stored procedure do I need to write Sql?

Question

SQL

There is a database table of the form
RowID(identifier) ​​ParentRowID(identifier) ​​Name (varchar(50))
1 NULL “Name 1”
2 1 “Name 2”
3 1 “Name 3”
4 1 “Name 4”
5 2 “Name 5”
6 2 “Name 6”
7 3 “Name 7”
….. “Name …”
N 1 “Name N”
It contains an unknown number of lines. Rows refer to each other, forming a tree-like structure (through the RowID and ParentRowID fields).
It is necessary to write a script or a stored procedure (preferably with MSSQL Server SQL syntax), which, as a result of the work, expands the tree structure for the application.
Several solutions are welcome with explanations of which one is preferable.
A description of other options for storing such a data structure is also welcome. With explanations of how they are better or worse.

Answer 1

if a large nesting level is planned, then it is better to make a tree using the nested set algorithm.
and so - we read:
mikehillyer.com/articles/managing-hierarchical-dat...