翻譯自：

http://www.mssqltips.com/sqlservertip/1455/using-the-case-expression-instead-of-dynamic-sql-in-sql-server/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=2012620

問題：

在決定IF/ELSE時，會有很多UPDATE查詢，某些情況下我使用游標，但循環幾千行數據的UPDATE時，會花費非常多的事件。我也使用一些動態SQL來處理一些查詢參數。除此之外，還有更好的選擇嗎？

?

?

解決方案：

CASE表達式是在解決SQLServer查詢問題上的一個強大的工具。你可能感覺到它在SELECT語句中的用法類似于IF/ELSE的處理。但是，相對與IF/ELSE，CASE表達式卻沒有那么多限制。

在以下代碼中將展示CASE表達式的用處：

l? 消除在UPDATE行時的游標循環。

l? 在使用聚集函數時，執行特殊處理。

l? 不使用動態SQL的動態ORDER BY 和WHERE子句

讓我們看看以下例子：

首先，先創建一個名為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

?

示例1：

由于報表展示的需要，在一個非范式化的表中增加一個所在州描述列。現在，你可以使用游標和來循環更新每一行。但是游標往往是性能殺手。你也可以使用大量UPDATE語句，但是這將導致程序非常臃腫。

對此，可以在一個UDPATE語句的SET 子句中使用帶有CASE關鍵字來實現更有效的操作：

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? ?

?

示例 2 ：

當我們需要統計所有來自 Massachusetts 州用戶的數量及他們的平均總消費時。我們能限制查詢在僅僅是 Massachusetts 的客戶。但這將使得在得到用戶總數時語句變得臃腫，為此，可以在聚集函數中使用 CASE 表達式來得到特定信息：

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?

因為在聚集函數中，NULL值不參與計算，所以可以通過這個特性來獲得我們想要的數據。

?

示例3：

??? 第三個案例來自于我們的桌面，我們需要一個存儲過程來被應用程序調用，但用戶想根據第一個名字或者第二個名字排序。其中一個方法是使用動態SQL來解決這個問題，但是我們可以使用CASE來等價實現：

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'

?

示例4：

???????? 最后一個例子中與示例3相似，我們需要改動存儲過程去查找特定州的客戶，如果該參數被忽略，則返回所有客戶的所在州。

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'?

使用CASE表達式替代SQL Server中的動態SQL