使用SQL Server CASE表達式代替動態(tài)SQL
SQL Server CASE表達式可以代替動態(tài)SQL語句，使進行大量更新查詢操作時可以提高查詢的效率，下面就讓我們一起了解一下使用SQL Server CASE表達式代替動態(tài)SQL的方法。
需要進行大量的SQL更新查詢，而這些查詢都需要我做出IF/ELSE之類的決定。我現(xiàn)在有的情況會使用光標，但是這樣搜查上千行查找更新需要花很長時間。有時候也使用一些動態(tài)SQL來決定某些查詢參數(shù)。但是有沒有更好的方法可以使用呢?
CASE語句是一個非常強大而有用的工具，你可以用它來解決你的SQL Server查詢問題。你可能已經(jīng)可以很熟練地在執(zhí)行SELECT命令的時候使用它來模擬IF/ELSE從句處理。不過，它的功用可遠遠不限于這類型的處理。
CASE表達式可以用來：
用于更新行的時候，避免使用光標回路
在使用合計函數(shù)的時候執(zhí)行專門的處理
創(chuàng)建動態(tài)ORDER BY和WHERE從句而無需使用動態(tài)SQL
現(xiàn)在讓我們來看看一些應用例子：
首先，新建一個名為Customer的表，插入一些行：
CREATE TABLE dbo.Customer
(
customerid INT IDENTITY PRIMARY KEY,
firstname VARCHAR(40) NOT NULL,
lastname VARCHAR(40) NOT NULL,
statecode VARCHAR(2) NOT NULL,
totalsales money NOT NULL DEFAULT 0.00
)
INSERT INTO dbo.Customer (firstname, lastname, statecode, totalsales)
SELECT 'Thomas', 'Jefferson', 'VA', 100.00
INSERT INTO dbo.Customer (firstname, lastname, statecode, totalsales)
SELECT 'John', 'Adams', 'MA', 200.00
INSERT INTO dbo.Customer (firstname, lastname, statecode, totalsales)
SELECT 'Paul', 'Revere', 'MA', 300.00
INSERT INTO dbo.Customer (firstname, lastname, statecode, totalsales)
SELECT 'Ben', 'Franklin', 'PA', 400.00
GO
例一
現(xiàn)在遇到一個需要向表添加一個狀態(tài)描述列用于生成所需報表的要求。你當然可以使用指針掃描整個表，達到更新每一行的目的，但是這樣做很花時間，降低系統(tǒng)性能表現(xiàn)。你也可以創(chuàng)建多個UPDATE語句，但這樣也好不到哪里去。但是，你可以把一個UPDATE語句和CASE結合使用，這樣可以只需要一個SET操作就能夠高效率地更新整個表。
ALTER TABLE dbo.Customer ADD statedescription VARCHAR(50) NULL
GO
UPDATE dbo.Customer
SET stateDescription = CASE WHEN statecode = 'MA' THEN 'Massachusetts'
WHEN statecode = 'VA' THEN 'Virginia'
WHEN statecode = 'PA' THEN 'Pennsylvania'
ELSE NULL
END
例二
現(xiàn)在我們又接到第二個請求，需要報告所有客戶的總數(shù)、Massachusetts客戶的總數(shù)以及所有Massachusetts客戶的平均銷量。我們當然可以把查詢范圍限制為Massachusetts客戶，但是這樣要獲得所有客戶總數(shù)就很麻煩。要解決這個問題，你可以編寫一個在合計函數(shù)里使用CASE表達的查詢，就能夠獲得Massachusetts客戶的信息了：
SELECT COUNT(*) AS TotalCustomers,
SUM(CASE WHEN statecode = 'MA' THEN 1 ELSE NULL END) AS TotalMassCustomers,
AVG(CASE WHEN statecode = 'MA' THEN totalsales ELSE NULL END) AS TotalMassSales
FROM dbo.Customer
因為執(zhí)行合計函數(shù)的時候會忽略NULL值，我們可以輕松獲得需要的總數(shù)。
現(xiàn)在又來了新的請求。我們需要一個可以被應用程序調(diào)用的存儲過程，但是用戶希望能夠可以通過first name或last name來排序。你可能會想要用動態(tài)SQL來解決這個問題，不過我們還可以使用CASE來代替動態(tài)SQL:
CREATE PROCEDURE dbo.getCustomerData @sortby VARCHAR(9), @sortdirection CHAR(4)
AS
SET nocount ON
SELECT customerid, firstname, lastname, statecode, statedescription, totalsales
FROM dbo.Customer
ORDER BY
CASE @sortdirection
WHEN 'asc' THEN
CASE @sortby
WHEN 'firstname' THEN firstname
WHEN 'lastname' THEN lastname
END
END
ASC,
CASE @sortdirection
WHEN 'desc' THEN
CASE @sortby
WHEN 'firstname' THEN firstname
WHEN 'lastname' THEN lastname
END
END
DESC
GO
EXEC dbo.getCustomerData 'lastname', 'desc'
例四
最后一個請求。我們需要修改上述的存儲過程來達到通過某個特定狀態(tài)來查找客戶。如果客戶狀態(tài)被省略，則返回所有狀態(tài)的客戶。
ALTER PROCEDURE dbo.getCustomerData @sortby VARCHAR(9), @sortdirection CHAR(4), @statecode VARCHAR(2) = NULL
AS
SET nocount ON
SELECT customerid, firstname, lastname, statecode, statedescription, totalsales
FROM dbo.Customer
WHERE statecode = CASE WHEN @statecode IS NOT NULL THEN @statecode
ELSE statecode
END
ORDER BY
CASE @sortdirection
WHEN 'asc' THEN
CASE @sortby
WHEN 'firstname' THEN firstname
WHEN 'lastname' THEN lastname
END
END
ASC,
CASE @sortdirection
WHEN 'desc' THEN
CASE @sortby
WHEN 'firstname' THEN firstname
WHEN 'lastname' THEN lastname
END
END
DESC
GO
EXEC dbo.getCustomerData 'lastname', 'desc', 'MA'
你可以根據(jù)下面幾條小提示來熟悉CASE的利用：
在線閱讀SQL Server 2000和2005關于CASE表達的內(nèi)容。
檢查你的數(shù)據(jù)庫更新代碼，看有沒有可以利用UPDATE…CASE處理的指針回路。
測評一下你的復雜的動態(tài)SQL邏輯看看是否可以利用CASE表達。
【編輯推薦】
