Bir projede alt-üst departman ilişkisini tanımlamak ve hiyerarşik olarak yukarıda bulunan bir departmanın, tüm alt departmanları izlemesini sağlamak üzere bir yapı kurmamız gerekti. Bir araştırma yaptık ve recursive (özyinelemeli) işlemlerde kullanılan CTE(Common Table Expression) yapılarının işimize yaracağını gördük.

CTE yapıları, kullanım biçimleri view veya türemiş tablolara benzeyen, fiziksel olarak saklanmayan anlık olarak oluşturulan, yalnızca bir kere tanımlanıp aynı kod bloğunda birden fazla kullanılabilen özellikle recursive(özyinelemeli) işlemlerde performans sağlayan yapılardır demiştik.

Şimdi bunu bir örnekle açıklamaya çalışalım. Önce Departman tablomuzu yaratalım ve örnek veri girişini tamamlayalım.

CREATE TABLE [dbo].[Departments](
	[DepartmentID] [int] IDENTITY(1,1) NOT NULL,
	[DepartmentName] [varchar](64) COLLATE Turkish_CI_AS NOT NULL,
	[ParentDepartmentID] [int] NULL
)

INSERT INTO Departments SELECT 'Genel Müdürlük',NULL
INSERT INTO Departments SELECT 'Finans',1
INSERT INTO Departments SELECT 'İnsan Kaynakları',1
INSERT INTO Departments SELECT 'IT',1
INSERT INTO Departments SELECT 'Muhasebe',2
INSERT INTO Departments SELECT 'Çalışan Yönetimi',3
INSERT INTO Departments SELECT 'Operasyonel İşler',4
INSERT INTO Departments SELECT 'Geliştirme',4

Belirlediğimiz bir departman ve hiyerarşik düzende bu departmana bağlı tüm departmanları listeleyebileceğimiz fonksiyon şu şekilde olmalıdır.

CREATE FUNCTION [dbo].[DisplayHierarchicalDepartments] 
( @StartDept int ) 
RETURNS @TableOfValues TABLE
(  DepartmentID int
,  DepartmentName varchar(64) ) 
AS 
BEGIN
	 
	;WITH CTE (DepartmentID, ChildDepartmentID)
	AS
	(
			-- Anchor member definition
		SELECT   DepartmentID = @StartDept,ChildDepartmentID = DepartmentID
		FROM Departments
		WHERE ParentDepartmentID = @StartDept
	 
		UNION ALL
	 
			-- Recursive member definition
		SELECT  Tmp.DepartmentID, Tmp.ChildDepartmentID
		FROM    (SELECT	 DepartmentID = CTE.ChildDepartmentID
				,ChildDepartmentID = TD.DepartmentID
				FROM CTE
		INNER JOIN Departments TD ON 
		TD.ParentDepartmentID = CTE.ChildDepartmentID
			) Tmp
		)

	INSERT INTO @TableOfValues SELECT DISTINCT  DepartmentID , DepartmentName	
       FROM Departments TE
	WHERE (TE.DepartmentID = @StartDept OR 
	EXISTS (SELECT 1 FROM CTE WHERE DepartmentID = TE.DepartmentID 
	OR ChildDepartmentID = TE.DepartmentID))
	order by DepartmentID
      
	RETURN 
      

END 

Sonuç olarak Genel Müdürlük veya IT departmanlarına bağlı tüm alt departmanları listelemek istediğimizde aşağıdaki sorguyu çalıştırmamız yeterli.

SELECT * FROM DisplayHierarchicalDepartments(1) -- Genel Müdürlük
SELECT * FROM DisplayHierarchicalDepartments(4) --IT

09.04

Zeki Volkan İşcen