動態交叉表 (動態樞紐) 常用於 **屬性值模式 (EAV)** 或不定欄位報表。本文解析一個使用**游標遍歷屬性定義表、動態拼接 `CASE WHEN` 聚合列、執行動態 SQL** 的存儲過程實作,並提供現代化改寫方案 (無游標、參數化、防注入)。
CREATE PROCEDURE P_GetSeriesProductDetail
@series_guid VARCHAR(40),
@condition VARCHAR(1000) = ''
AS
BEGIN
DECLARE @ParamNo NVARCHAR(5), @SQL NVARCHAR(4000) = N'';
-- 1. 遊標遍歷該系列所有屬性參數編號
DECLARE P_cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT param_no FROM V_product_params WHERE series_guid = @series_guid;
OPEN P_cursor;
FETCH NEXT FROM P_cursor INTO @ParamNo;
WHILE @@FETCH_STATUS = 0
BEGIN
-- 2. 動態拼接每個屬性的 CASE WHEN 聚合列
-- MAX(CASE param_no WHEN 'P1' THEN param_value ELSE '' END) AS F_P1
SET @SQL += N',MAX(CASE param_no WHEN ' + @ParamNo + N' THEN param_value ELSE N'''' END) AS F' + @ParamNo + NCHAR(13);
FETCH NEXT FROM P_cursor INTO @ParamNo;
END
CLOSE P_cursor; DEALLOCATE P_cursor;
-- 3. 組裝完整查詢
SET @SQL = N'SELECT type_guid, series_guid, product_no, product_name' + @SQL + N'
FROM V_product_params
WHERE series_guid = ' + QUOTENAME(@series_guid, N'''') +
CASE WHEN LTRIM(@condition) <> N'' THEN N' AND ' + @condition ELSE N'' END + N'
GROUP BY type_guid, series_guid, product_no, product_name';
PRINT @SQL; -- 除錯用
EXEC sp_executesql @SQL; -- 執行動態 SQL
END
| 技術點 | 說明 | 風險/缺陷 |
|---|---|---|
| 游標 | 逐行處理屬性定義行 | **效能差**、佔用資源、鎖定系統表、難除錯。現代 SQL 可用 `STRING_AGG` / `FOR XML PATH` 替代。 |
| 動態 SQL 拼接 | 構建 `CASE WHEN` 列清單 | **SQL 注入風險** (`@condition` 直接拼接);`NVARCHAR(4000)` 易溢位 (屬性多時);`PRINT` 截斷 8000 字元難除錯。 |
| `QUOTENAME` 缺失 | `@series_guid` 用 `QUOTENAME` 防注入,但 `@condition` 完全裸奔 | 高危!`@condition` 應改為參數化或嚴格白名單驗證。 |
| `MAX(CASE...)` 模式 | 將行屬性轉為列,`GROUP BY` 產品主鍵 | 標準關係型轉置寫法,效能尚可 (單掃描)。 |
| 隱式轉換 | `param_no` 可能為數值型,拼接字串隱式轉換 | 建議顯式 `CAST(@ParamNo AS NVARCHAR(10))`。 |