原文:使用CASE表达式替代SQL Server中的动态SQL
翻译自：
问题：
在决定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'