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

朴灿烈づ我的快乐病毒、 2022-03-31 13:56 202阅读 0赞

原文: [  使用CASE表达式替代SQL Server中的动态SQL ][_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][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

示例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)

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

ASC,

CASE @sortdirection

WHEN  'desc' THEN

CASE  @sortby

WHEN  'firstname' THEN  firstname

WHEN  'lastname' THEN lastname

END

DESC

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'

[_CASE_SQL Server_SQL]: http://blog.csdn.net/dba_huangzj/article/details/7684520
[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]: 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