原文:使用CASE表达式替代SQL Server中的动态SQL

翻译自：

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'