動態 SQL 威力展示 (2) - “類 ‧ 多維度分析”開發實例
在構思本文之初，我確實想過直接將之前寫的程式放上來，但幾經思考，最後決定多花點時間改用 Adventure Works 範例資料庫為場景，儘量模擬我當時開發的過程，這樣可以方便想測試的人使用，也因此僅著重在 T-SQL 撰寫技巧，其他部分暫且略過不提，若有甚麼不周之處，諸君莫怪。
## 警告
本主題包含的所有範例僅為概念展示，並沒有全面性的規劃與防範可能的漏洞，不建議直接使用於實務環境，你應當自行評估且加以修改補強，特別是動態 SQL 非常容易遭受 SQL Injection 攻擊。
模擬案例提要
故事是這樣開始的，H 君於「探險工房戶外旅遊專賣店」擔任程式開發人員一職 (公司名稱洋文據說是 Adventure Works Equipments，真巧！) ，前陣子幫公司完成了自主開發的資訊系統，堪能滿足日常作業所需。不久，總經理意識到以前沒有系統時，各部門同事的報表多半透過試算表軟體自行維護相關數據，現在系統有了，為求提升營運效益，指示 H 君必須竭盡全力將各單位所需報表接續開發出來，其中需求急切者莫過於業務部門的銷售業績報表。單純的解決方案
初步訪談後，H 君了解業務主管目前最想看的是各家店櫃銷售狀況，包含：時間區間、特定店櫃、銷售人員等基本篩選條件，需求算是很明確，難度也不高，這對有練過的 H 君來說無疑是小事一樁，很快就寫出一支預存程序可以撈出店櫃銷售資料：
@Store nvarchar(48), -- 店櫃名稱
@SalesPerson nvarchar(32), -- 銷售人員
@StartDate datetime, -- 起始日期
@EndDate datetime, -- 截止日期
@StartYear int, -- 起始年度
@EndYear int -- 截止年度
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@cmd nvarchar(1024), -- 查詢字串
@filter nvarchar(192) -- 篩選字串
SET @cmd = N'
SELECT
S.Name AS Store
, SUM(SOD.OrderQty) AS OrderQty, SUM(SOD.LineTotal) AS SubTotal, SUM(dbo.ufnGetProductStandardCost(P.ProductID, SO.OrderDate) * SOD.OrderQty) AS CostTotal
, SUM((SOD.UnitPrice - dbo.ufnGetProductStandardCost(P.ProductID, SO.OrderDate)) * SOD.OrderQty) AS [Gross Profit]
, SUM((SOD.UnitPrice - dbo.ufnGetProductStandardCost(P.ProductID, SO.OrderDate)) * SOD.OrderQty) / SUM(SOD.LineTotal) * 100 AS [Gross Margin %]
FROM Sales.Store AS S
INNER JOIN Sales.SalesOrderHeader AS SO ON S.CustomerID = SO.CustomerID
INNER JOIN Sales.SalesOrderDetail AS SOD ON SO.SalesOrderID = SOD.SalesOrderID
INNER JOIN Production.Product AS P ON SOD.ProductID = P.ProductID
INNER JOIN Sales.vSalesPerson AS SP ON SO.SalesPersonID = SP.SalesPersonID
WHERE (1 = 1)
{0}
GROUP BY
S.Name;';
SET @filter = N'';
-- 組合篩選字串
IF (LEN(@Store) > 0)
BEGIN
SET @filter = @filter + N'AND (S.Name = @Store) ';
END
IF (LEN(@SalesPerson) > 0)
BEGIN
SET @filter = @filter + N'AND (SP.FirstName + N'' '' + SP.LastName = @SalesPerson) ';
END
IF (LEN(@StartDate) > 0 AND LEN(@EndDate) > 0)
BEGIN
SET @filter = @filter + N'AND (SO.OrderDate BETWEEN @StartDate AND @EndDate) ';
END
IF (@StartYear > 0 AND @EndYear > 0)
BEGIN
SET @filter = @filter + N'AND (YEAR(SO.OrderDate) BETWEEN @StartYear AND @EndYear) ';
END
-- 置換篩選條件
SET @cmd = REPLACE(@cmd, N'{0}', @filter);
DECLARE @Stmt nvarchar(1024), -- 動態查詢
@Params nvarchar(144) -- 參數定義
SET @Stmt = @cmd;
SET @Params = N'@Store nvarchar(50), @SalesPerson nvarchar(50), @StartDate datetime, @EndDate datetime, @StartYear int, @EndYear int ';
-- 執行動態查詢
EXEC dbo.sp_executesql
@Stmt,
@Params,
@Store,
@SalesPerson,
@StartDate,
@EndDate,
@StartYear,
@EndYear;
-- 印出動態查詢(Debug 用)
PRINT @Stmt + CHAR(10) + N'(statement length：' + CAST(LEN(@Stmt) AS nvarchar(5))+ N')';
END
GO回傳的結果如下(僅列部分)，欄位依序是店櫃、銷量、銷額、成本、毛利、毛利率：
其中採用動態 SQL 的理由是，可針對使用者輸入的篩選值去動態組合過濾條件，接著透過 REPLACE 函數把篩選條件塞到 WHERE 子句底下預留的 {0} 區段，算是 SQL 版的 Formatting String 做法 (鄉野鄙人說著自爽的，像不像就別太在意了 XD)，好處是主結構與邏輯分開來，後續維護便直覺多了！
不變的道理 - 「Change」
都怪 H 君好傻好天真，正計劃要找另一個單位訪談之際，業務單位馬上有了新需求，說上一支報表看的是店櫃，那想看「銷售員業績報表」似乎不能用，看看時間還有，趕快從頭再做一個吧…，於是同樣的作法、同樣的邏輯、不同的角度，H 君再度產出了第二支程序，除第一欄改為銷售員以外，其餘欄位皆同，回傳結果如下：
雖然還是解決了問題，但 H 君似乎開始感到有些許的不安 (程式設計師的第六感？)，眼看使用者的報表需求即將如雪片般飛來，預料多數報表欄位又將大同小異，為了節能、減碳、救地球，想出因應之道刻不容緩！
科技，始終來自於惰性...
程式寫久了常會聽到要預留彈性不要寫死，但 H 君比較在意的是會不會爆肝而死。就前述案列來說，好像應該想辦法再把甚麼東西抽離出來，因為銷售報表要看的大概就是那些，只是常常有不同的分析角度，若真的任由 User 改個欄位就再開發一支報表，恐怕寫到民國一百年也寫不完！…這促使 H 君想要做到類似 OLAP 線上分析系統，多個維度的資料彙總檢視一樣，如此一來類似的報表全部集縮到同一支程序去撈資料，查詢介面也只需寫一次，精簡程式碼帶來的直接效益就是工作量可以大大減少，讓我們來看看要怎麼修改：
@Store nvarchar(48), -- 店櫃名稱
@SalesPerson nvarchar(32), -- 銷售人員
@StartDate datetime, -- 起始日期
@EndDate datetime, -- 截止日期
@StartYear int, -- 起始年度
@EndYear int, -- 截止年度
@Dimension int -- 查詢維度(1：店櫃別；2：銷售員別；3：年度別)
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@cmd nvarchar(1024), -- 查詢字串
@dim nvarchar(40), -- 維度字串
@filter nvarchar(192) -- 篩選字串
SET @cmd = N'
SELECT
{d} AS Dimension
, SUM(SOD.OrderQty) AS OrderQty, SUM(SOD.LineTotal) AS SubTotal, SUM(dbo.ufnGetProductStandardCost(P.ProductID, SO.OrderDate) * SOD.OrderQty) AS CostTotal
, SUM((SOD.UnitPrice - dbo.ufnGetProductStandardCost(P.ProductID, SO.OrderDate)) * SOD.OrderQty) AS [Gross Profit]
, SUM((SOD.UnitPrice - dbo.ufnGetProductStandardCost(P.ProductID, SO.OrderDate)) * SOD.OrderQty) / SUM(SOD.LineTotal) * 100 AS [Gross Margin %]
FROM Sales.Store AS S
INNER JOIN Sales.SalesOrderHeader AS SO ON S.CustomerID = SO.CustomerID
INNER JOIN Sales.SalesOrderDetail AS SOD ON SO.SalesOrderID = SOD.SalesOrderID
INNER JOIN Production.Product AS P ON SOD.ProductID = P.ProductID
INNER JOIN Sales.vSalesPerson AS SP ON SO.SalesPersonID = SP.SalesPersonID
WHERE (1 = 1)
{f}
GROUP BY
{d};';
-- 切換彙總維度
SELECT @dim =
CASE
WHEN (@Dimension = 1)
THEN N'S.Name'
WHEN (@Dimension = 2)
THEN N'SP.FirstName + N'' '' + SP.LastName'
WHEN (@Dimension = 3)
THEN N'YEAR(SO.OrderDate)'
END;
SET @filter = N'';
-- 組合篩選字串
IF (LEN(@Store) > 0)
BEGIN
SET @filter = @filter + N'AND (S.Name = @Store) ';
END
IF (LEN(@SalesPerson) > 0)
BEGIN
SET @filter = @filter + N'AND (SP.FirstName + N'' '' + SP.LastName = @SalesPerson) ';
END
IF (LEN(@StartDate) > 0 AND LEN(@EndDate) > 0)
BEGIN
SET @filter = @filter + N'AND (SO.OrderDate BETWEEN @StartDate AND @EndDate) ';
END
IF (@StartYear > 0 AND @EndYear > 0)
BEGIN
SET @filter = @filter + N'AND (YEAR(SO.OrderDate) BETWEEN @StartYear AND @EndYear) ';
END
-- 置換篩選條件
SET @cmd = REPLACE(REPLACE(@cmd, N'{d}', @dim), N'{f}', @filter);
DECLARE @Stmt nvarchar(1024), -- 動態查詢
@Params nvarchar(144) -- 參數定義
SET @Stmt = @cmd;
SET @Params = N'@Store nvarchar(50), @SalesPerson nvarchar(50), @StartDate datetime, @EndDate datetime, @StartYear int, @EndYear int ';
-- 執行動態查詢
EXEC dbo.sp_executesql
@Stmt,
@Params,
@Store,
@SalesPerson,
@StartDate,
@EndDate,
@StartYear,
@EndYear;
-- 印出動態查詢(Debug 用)
PRINT @Stmt + CHAR(10) + N'(statement length：' + CAST(LEN(@Stmt) AS nvarchar(5))+ N')';
END
GO調整的地方不多，只是多加入了查詢維度的判斷，同樣的 Formatting String 手法再度出現，這次是用來切換彙總維度將所需資訊塞入 {d} 區段，原來的篩選條件區段由 {0} 改為 {f} 以利識別 (d 是指 dimension，f 代表 filter)。可以說自從運用了這樣的技巧後，H 君再也不怕使用者變換莫測的需求，每天都能準時下班回家抱老婆玩小孩了呢！(注意：老婆跟小孩應依長幼順序不可顛倒，否則本篇就不算技術文…)
告一段落
本篇的目的是藉由實例讓大家牛刀小試一下，親自感受動態 SQL 的靈活度，各位不妨嘗試看看改用靜態查詢來做會面臨甚麼樣的狀況，假如使用者的需求還包括可以任意指定排序依據等更複雜的變化呢？…我想絕對不輕鬆就是了。當然還是老話一句：凡事過猶不及，重點是在適當的時機，特別是具有反覆性或相似性質高的邏輯處理中，撰寫動態 SQL 確實有助於縮短開發時程。不知道今天分享的內容，各位是否有收穫呢？在此預告下一篇要跟大家聊聊動態 SQL 幾個值得深入探討的議題，咱們下回見！