使用SQL Server CASE表達(dá)式代替動(dòng)態(tài)SQL
SQL Server CASE表達(dá)式可以代替動(dòng)態(tài)SQL語(yǔ)句，使進(jìn)行大量更新查詢操作時(shí)可以提高查詢的效率，下面就讓我們一起了解一下使用SQL Server CASE表達(dá)式代替動(dòng)態(tài)SQL的方法。
需要進(jìn)行大量的SQL更新查詢，而這些查詢都需要我做出IF/ELSE之類(lèi)的決定。我現(xiàn)在有的情況會(huì)使用光標(biāo)，但是這樣搜查上千行查找更新需要花很長(zhǎng)時(shí)間。有時(shí)候也使用一些動(dòng)態(tài)SQL來(lái)決定某些查詢參數(shù)。但是有沒(méi)有更好的方法可以使用呢?
CASE語(yǔ)句是一個(gè)非常強(qiáng)大而有用的工具，你可以用它來(lái)解決你的SQL Server查詢問(wèn)題。你可能已經(jīng)可以很熟練地在執(zhí)行SELECT命令的時(shí)候使用它來(lái)模擬IF/ELSE從句處理。不過(guò)，它的功用可遠(yuǎn)遠(yuǎn)不限于這類(lèi)型的處理。
CASE表達(dá)式可以用來(lái)：
用于更新行的時(shí)候，避免使用光標(biāo)回路
在使用合計(jì)函數(shù)的時(shí)候執(zhí)行專(zhuān)門(mén)的處理
創(chuàng)建動(dòng)態(tài)ORDER BY和WHERE從句而無(wú)需使用動(dòng)態(tài)SQL
現(xiàn)在讓我們來(lái)看看一些應(yīng)用例子：
首先，新建一個(gè)名為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)在遇到一個(gè)需要向表添加一個(gè)狀態(tài)描述列用于生成所需報(bào)表的要求。你當(dāng)然可以使用指針掃描整個(gè)表，達(dá)到更新每一行的目的，但是這樣做很花時(shí)間，降低系統(tǒng)性能表現(xiàn)。你也可以創(chuàng)建多個(gè)UPDATE語(yǔ)句，但這樣也好不到哪里去。但是，你可以把一個(gè)UPDATE語(yǔ)句和CASE結(jié)合使用，這樣可以只需要一個(gè)SET操作就能夠高效率地更新整個(gè)表。
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)在我們又接到第二個(gè)請(qǐng)求，需要報(bào)告所有客戶的總數(shù)、Massachusetts客戶的總數(shù)以及所有Massachusetts客戶的平均銷(xiāo)量。我們當(dāng)然可以把查詢范圍限制為Massachusetts客戶，但是這樣要獲得所有客戶總數(shù)就很麻煩。要解決這個(gè)問(wèn)題，你可以編寫(xiě)一個(gè)在合計(jì)函數(shù)里使用CASE表達(dá)的查詢，就能夠獲得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
因?yàn)閳?zhí)行合計(jì)函數(shù)的時(shí)候會(huì)忽略NULL值，我們可以輕松獲得需要的總數(shù)。
現(xiàn)在又來(lái)了新的請(qǐng)求。我們需要一個(gè)可以被應(yīng)用程序調(diào)用的存儲(chǔ)過(guò)程，但是用戶希望能夠可以通過(guò)first name或last name來(lái)排序。你可能會(huì)想要用動(dòng)態(tài)SQL來(lái)解決這個(gè)問(wèn)題，不過(guò)我們還可以使用CASE來(lái)代替動(dòng)態(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'
例四
最后一個(gè)請(qǐng)求。我們需要修改上述的存儲(chǔ)過(guò)程來(lái)達(dá)到通過(guò)某個(gè)特定狀態(tài)來(lá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ù)下面幾條小提示來(lái)熟悉CASE的利用：
在線閱讀SQL Server 2000和2005關(guān)于CASE表達(dá)的內(nèi)容。
檢查你的數(shù)據(jù)庫(kù)更新代碼，看有沒(méi)有可以利用UPDATE…CASE處理的指針回路。
測(cè)評(píng)一下你的復(fù)雜的動(dòng)態(tài)SQL邏輯看看是否可以利用CASE表達(dá)。
【編輯推薦】
