使用CASE表达式代替动态SQL(SQL Server)

使用CASE表达式代替动态SQL(SQL Server)
我需要进行大量的SQL更新查询，而这些查询都需要我做出IF/ELSE之类的决定。

我现在有的情况会使用光标，但是这样搜查上千行查找更新需要花很长时间。

我有时候也使用一些动态SQL来决定某些查询参数。

但是有没有更好的方法可以使用呢?
专家解答：
CASE语句是一个非常强大而有用的工具，你可以用它来解决你的SQL Server 查询问题。

你可能已经可以很熟练地在执行SELECT命令的时候使用它来模拟
IF/ELSE从句处理。

不过，它的功用可远远不限于这类型的处理。

CASE表达式可以用来：
∙用于更新行的时候，避免使用光标回路
∙在使用合计函数的时候执行专门的处理
∙创建动态ORDER BY和WHERE从句而无需使用动态SQL
现在让我们来看看一些应用例子：
首先，新建一个名为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
例一
现在遇到一个需要向表添加一个状态描述列用于生成所需报表的要求。

你当然可以使用指针扫描整个表，达到更新每一行的目的，但是这样做很花时间，降低系统性能表现。

你也可以创建多个UPDATE语句，但这样也好不到哪里去。

但是，你可以把一个UPDATE语句和CASE结合使用，这样可以只需要一个SET操作就能够高效率地更新整个表。

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
例二
现在我们又接到第二个请求，需要报告所有客户的总数、Massachusetts客户的总数以及所有Massachusetts客户的平均销量。

我们当然可以把查询范围限制为Massachusetts客户，但是这样要获得所有客户总数就很麻烦。

要解决这个问题，你可以编写一个在合计函数里使用CASE表达的查询，就能够获得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
因为执行合计函数的时候会忽略NULL值，我们可以轻松获得需要的总数。

现在又来了新的请求。

我们需要一个可以被应用程序调用的存储过程，但是用户希望能够可以通过first name或last name来排序。

你可能会想要用动态SQL来解决这个问题，不过我们还可以使用CASE来代替动态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'
例四
最后一个请求。

我们需要修改上述的存储过程来达到通过某个特定状态来查找客户。

如果客户状态被省略，则返回所有状态的客户。

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 2000和2005关于CASE表达的内容。

∙检查你的数据库更新代码，看有没有可以利用UPDATE…CASE处理的指针回路。

∙测评一下你的复杂的动态SQL逻辑看看是否可以利用CASE表达。