SQL 遞迴 CTE 實現組織架構樹 + 存儲過程 IN 參數傳遞方案：Split 函數 + 表值參數雙軌制

一、遞迴 CTE 查詢組織樹

-- 表：OrganiseUnit (OrganiseUnitID, ParentOrganiseUnitID, OrganiseName, OrganiseCode)
-- 目標：傳入根節點 ID，查詢該節點及所有後代名稱

DECLARE @RootID INT = 100;

WITH OrgTree AS (
    -- 錨點：根節點
    SELECT OrganiseUnitID, ParentOrganiseUnitID, OrganiseName, OrganiseCode, 0 AS Level
    FROM OrganiseUnit WHERE OrganiseUnitID = @RootID
    
    UNION ALL
    
    -- 遞迴：子節點
    SELECT ou.OrganiseUnitID, ou.ParentOrganiseUnitID, ou.OrganiseName, ou.OrganiseCode, ot.Level + 1
    FROM OrganiseUnit ou
    INNER JOIN OrgTree ot ON ou.ParentOrganiseUnitID = ot.OrganiseUnitID
)
SELECT OrganiseName, OrganiseCode FROM OrgTree
OPTION (MAXRECURSION 0);  -- 0 = 無限制，防預設 100 層截斷

二、存儲過程 IN 參數傳遞難題

需求：WHERE OrganiseCode IN ('A','B','C')，但條件來自應用層動態字串 'A,B,C'。

❌ 錯誤嘗試

DECLARE @codes VARCHAR(MAX) = 'A,B,C';
SELECT * FROM OrganiseUnit WHERE OrganiseCode IN (@codes);  
-- 視為單一值 'A,B,C'，非三個值

方案一：動態 SQL (EXEC) — 簡單但風險高

DECLARE @sql NVARCHAR(MAX) = N'SELECT * FROM OrganiseUnit WHERE OrganiseCode IN (' + @codes + N')';
EXEC (@sql);
-- 缺點：注入風險、計畫難重用、除錯困難、複雜邏輯拼接易錯

方案二：Split 函數 + IN 子查詢 (SQL 2005+ 通用) — 推薦

-- 1. 建立分割函數 (表值函數)
CREATE FUNCTION dbo.SplitString 
(
    @input NVARCHAR(MAX), 
    @delimiter NCHAR(1)
)
RETURNS @result TABLE (val NVARCHAR(4000))
AS
BEGIN
    DECLARE @start INT = 1, @pos INT;
    WHILE (1=1)
    BEGIN
        SET @pos = CHARINDEX(@delimiter, @input, @start);
        IF @pos = 0 
        BEGIN
            INSERT @result VALUES (SUBSTRING(@input, @start, LEN(@input)-@start+1));
            BREAK;
        END
        INSERT @result VALUES (SUBSTRING(@input, @start, @pos-@start));
        SET @start = @pos + 1;
    END
    RETURN;
END;
GO

-- 2. 使用
DECLARE @codes NVARCHAR(MAX) = N'A,B,C';
SELECT * FROM OrganiseUnit 
WHERE OrganiseCode IN (SELECT val FROM dbo.SplitString(@codes, N','));

優點：參數化安全、計畫可重用、集合操作、效能佳。
缺點：標量函數/多語句 TVF 無統計資訊，大量值時優化器估計偏差。

方案三：表值參數 (TVP, SQL 2008+) — 企業級首選

-- 1. 建立型別
CREATE TYPE dbo.CodeList AS TABLE (Code NVARCHAR(100) PRIMARY KEY);
GO

-- 2. SP 定義
CREATE PROC usp_GetByCodes @Codes dbo.CodeList READONLY
AS
    SELECT * FROM OrganiseUnit WHERE OrganiseCode IN (SELECT Code FROM @Codes);
GO

-- 3. C# 呼叫
var dt = new DataTable(); dt.Columns.Add("Code", typeof(string));
foreach (var c in codes) dt.Rows.Add(c);
var cmd = new SqlCommand("usp_GetByCodes", conn) { CommandType = CommandType.StoredProcedure };
var p = cmd.Parameters.AddWithValue("@Codes", dt);
p.SqlDbType = SqlDbType.Structured;
p.TypeName = "dbo.CodeList";
cmd.ExecuteNonQuery();

優點：強型別、統計資訊、並行計畫、無拼接、效能最優。

方案選型建議