SqlServer中遞迴擷取所有上層業務的名稱組合

今天在sqlserver中用到了自訂函數，而且是遞迴調用的純量值函式。原因是有一個需求，需要在列表裡面展示出部門的層級關係，看到原來是有一個V_User的視圖，於是採取最簡單的方案，建立一個純量值函式，在視圖中追加一列DeptFullName。用來擷取出部門的所有上層業務名稱。

其自訂函數如下：

CREATE FUNCTION [dbo].[Func_GetDeptName] (-- 當前部門的上層業務ID@deptPid int,-- 當前部門的名稱@deptName varchar(1000),-- 是否取到根部門@isRootName int)RETURNS varchar(1000)ASBEGINdeclare @temp varchar(50)declare @id intset @id = 0select @id=DEPARTMENT_PID,@temp=DEPARTMENT_Name from DEPARTMENT where DEPARTMENT_ID=@deptPidif 0 = @id and 1=@isRootName and 0 <> @deptPidset @deptName = @temp +' > '+ @deptName;else if @id<>0set @deptName =  dbo.Func_GetDeptName(@id,@temp,@isRootName)+' > '+ @deptName-- Return the result of the functionRETURN @deptName;END

考慮到，追加上跟節點公司名稱後，名稱太長的問題。特地在這裡設定了一個參數，作為是否顯示根節點的標誌。

使用如下：

select *,dbo.Func_GetDeptName(DEPARTMENT_PID,DEPARTMENT_NAME,1) as DeptFullName  from DEPARTMENT d

將來擴充一下，也可以將中間的串連符作為參數傳遞。