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

最新推荐文章于 2025-02-24 15:29:33 发布

你狗

最新推荐文章于 2025-02-24 15:29:33 发布

阅读量223

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：http://www.cnblogs.com/stu-acer/archive/2008/03/22/1117429.html

问题：

　　我需要进行大量的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的利用：