超強預存程序！原作，著作權！精悍！！！

　　在開發資料庫應用程式中，我們經常需要表示多層次關係的資料，譬如：MRP中的BOM，多層級的組織圖，多級的角色關係…… 在資料庫中描述這種關係時，我們通常使用父節點指向的方式來設計表結構，譬如對部門的表結構定義如下：

　　CREATE TABLE TDepartment
　　(
　　　　ParentNo varchar(25),    /* 上層業務編號，如果為NULL則表示目前記錄為頂級部門。 */
　　　　DepartmentNo varchar(25) PRIMARY KEY CLUSTERED, /* 當前部門編號，主關鍵字段。 */
　　　　Name  nvarchar(100) NOT NULL,   /* 部門名稱。 */
　　　　NameEx  nvarchar(100),    /* 副檔名稱。 */
　　　　Remark  nvarchar(500)    /* 備　　注。 */
　　)

　　功能需求：擷取指定部門編號及其所屬的所有或指定層次的下級部門的記錄集。

　　在以往的開發中，我們通常是在用戶端或者中介層來處理以上需求的，一般是通過程式設計語言（VB、Delphi、Java/.NET）來處理從資料庫返回的記錄集，這種方式通常需要從資料庫獲得整個表的記錄，因為我們不能確定給定主鍵所具有的哪些下級記錄，然後再在編程代碼中使用遞迴的方式來擷取相關的下級記錄。這種使用遞迴的方式，通常難以避免資料中非法記錄所引起的死迴圈問題（當然，你也可以處理這種問題，只是有些麻煩，而且通常也不夠優美）。那麼，能不能在資料庫中就處理好這個問題呢，這樣既能減少不必要的資料轉送，又能避免使用遞迴所帶來的負面影響，最好還能自動屏蔽掉非法資料指向的問題（目前記錄的父指標欄位指向其某個下級記錄）。
　　呵呵，賣弄完畢，亮出匕首（代碼，Transact-SQL/MS-SQLServer 2000）：

/* 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，打完收工。短小精悍！！！

　　你可以使用或部分引用該段代碼，不過必須保留以上著作權注釋部分內容，並注釋代碼來源，否則將保留追究侵權的權利！

　　如果你發現有BUG請來信與我聯絡(SW515@21cn.com)，謝謝合作。