翻譯自：

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 @sortbyVARCHAR(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'