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

jialiguo

　　翻译自：
　　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 TABLEdbo.Customer
　　(
　　customerid INT IDENTITY PRIMARYKEY,
　　firstname VARCHAR(40)NOT NULL,
　　lastname VARCHAR(40)NOT NULL,
　　statecode VARCHAR(2)NOT NULL,
　　totalsales money NOT NULLDEFAULT 0.00
　　)
　　
　　INSERT INTOdbo.Customer(firstname,lastname, statecode, totalsales)
　　SELECT 'Thomas','Jefferson', 'VA', 100.00
　　
　　INSERT INTOdbo.Customer(firstname,lastname, statecode, totalsales)
　　SELECT 'John','Adams', 'MA', 200.00
　　
　　INSERT INTOdbo.Customer(firstname,lastname, statecode, totalsales)
　　SELECT 'Paul','Revere', 'MA', 300.00
　　
　　INSERT INTOdbo.Customer(firstname,lastname, statecode, totalsales)
　　SELECT 'Ben','Franklin', 'PA', 400.00
　　GO
　　
　　示例1：
　　由于报表展示的需要，在一个非范式化的表中增加一个所在州描述列。现在，你可以使用游标和来循环更新每一行。但是游标往往是性能杀手。你也可以使用大量UPDATE语句，但是这将导致程序非常臃肿。
　　对此，可以在一个UDPATE语句的SET 子句中使用带有CASE关键字来实现更有效的操作：
　　ALTERTABLEdbo.CustomerADDstatedescriptionVARCHAR(50)NULL
GO
UPDATEdbo.Customer
SETstateDescription=CASEWHENstatecode='MA'THEN'Massachusetts'
WHENstatecode='VA'THEN'Virginia'
WHENstatecode='PA'THEN'Pennsylvania'
ELSENULL
END
　　
　　示例2：
　　当我们需要统计所有来自Massachusetts州用户的数量及他们的平均总消费时。我们能限制查询在仅仅是Massachusetts的客户。但这将使得在得到用户总数时语句变得臃肿，为此，可以在聚集函数中使用CASE表达式来得到特定信息：
　　SELECTCOUNT(*)ASTotalCustomers,
SUM(CASEWHENstatecode='MA'THEN1ELSENULLEND)ASTotalMassCustomers,
AVG(CASEWHENstatecode='MA'THENtotalsalesELSENULLEND)ASTotalMassSales
FROMdbo.Customer
　　因为在聚集函数中，NULL值不参与计算，所以可以通过这个特性来获得我们想要的数据。
　　
　　示例3：
　　第三个案例来自于我们的桌面，我们需要一个存储过程来被应用程序调用，但用户想根据第一个名字或者第二个名字排序。其中一个方法是使用动态SQL来解决这个问题，但是我们可以使用CASE来等价实现：
　　CREATE PROCEDUREdbo.getCustomerData@sortbyVARCHAR(9),@sortdirection CHAR(4)
　　AS
　　SET nocountON
　　
　　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相似，我们需要改动存储过程去查找特定州的客户，如果该参数被忽略，则返回所有客户的所在州。
　　ALTERPROCEDUREdbo.getCustomerData@sortbyVARCHAR(9),@sortdirectionCHAR(4),@statecodeVARCHAR(2)=NULL
AS
SETnocountON

SELECTcustomerid,firstname,lastname,statecode,statedescription,totalsales
FROMdbo.Customer
WHEREstatecode=CASEWHEN@statecodeISNOTNULLTHEN@statecode
ELSEstatecode
END
ORDERBY
CASE@sortdirection
WHEN'asc'THEN
CASE@sortby
WHEN'firstname'THENfirstname
WHEN'lastname'THENlastname
END
END
ASC,
CASE@sortdirection
WHEN'desc'THEN
CASE@sortby
WHEN'firstname'THENfirstname
WHEN'lastname'THENlastname
END
END
DESC
GO

EXECdbo.getCustomerData'lastname','desc','MA'