Obtains the record set of the specified department number and all or lower-level departments to which it belongs.

In developing database applications, we often need to represent multi-level relational data, such as BOM in MRP, multi-level organizational structure, multi-level role relationship ...... When describing this relationship in a database, we usually design the table structure by pointing to the parent node. For example, the table structure of a department is defined as follows:

Create Table tdepartment
(
Parentno varchar (25),/* superior department number. If it is null, the current record is a top-level department. */
Departmentno varchar (25) primary key clustered,/* current department ID, master key field. */
Name nvarchar (100) not null,/* Department name. */
Nameex nvarchar (100),/* extension name. */
Remark nvarchar (500)/* slave note. */
)

　　Function Requirement: Obtain the record set of the specified department number and all or lower-level departments to which it belongs.

In the past development, we usually deal with the above requirements on the client or the middle layer, usually through programming languages (VB, Delphi, Java /.. net) to process the record set returned from the database. This method usually requires obtaining the record of the entire table from the database, because we cannot determine which sub-records the given primary key has, then, we can use recursion in programming code to obtain relevant subordinate records. This recursive method is usually difficult to avoid the endless loop problem caused by illegal records in the data (of course, you can also solve this problem, but it is a little troublesome and generally not elegant enough ). So can we solve this problem in the database? This will not only reduce unnecessary data transmission, but also avoid the negative impact of recursion, it is better to automatically block the issue of pointing illegal data (the parent pointer field of the current record points to a lower-level record ).
Haha, after the show is completed, the dagger (Code, transact-SQL/MS-sqlserver 2000) is shown ):

/* Copyright All (c) 2004 zhongfeng, http://blog.csdn.net/SW515 */
Create procedure DBO. pselectdepartmenthierarchy
@ Departmentno varchar (25 ),
@ Depth Int =-1
As
Set nocount on

Declare @ tstack table
(
Indicator varchar (50)
)

Declare @ index int
Set @ Index = 0

Insert into @ tstack
Select departmentno
From DBO. tdepartment
Where departmentno like @ departmentno

While @ rowcount> 0 and (@ index <@ depth or @ depth <0)
Begin
Set @ Index = @ index + 1

Insert into @ tstack
Select departmentno
From DBO. tdepartment
Where
Parentno in (select indicator from @ tstack) and
Departmentno not in (select indicator from @ tstack)
End

Select DBO. tdepartment .*
From @ tstack as t
Inner join DBO. tdepartment on
T. Indicator = DBO. tdepartment. departmentno
Go

 

　　OK. Short and cool !!!

　　You can use or partially reference this part of the code, but you must retain the above copyright comments and comment out the code source. Otherwise, you will be entitled to pursue infringement!

　　If you find any bugs, please send me a letter to contact me (SW515@21cn.com). Thank you for your cooperation.