使用CHOOSE使用举例-动态排序字段

CHOOSE在SQL Server2012后才开始支持。在2012前需要用CASE实现

用法：https://msdn.microsoft.com/en-us/library/hh213019.aspx

print CHOOSE (abs(checksum(newid()))%5+1 , 'A','B','C','D','E')go 10

Beginning execution loop

A

 

D

A

E

D

A

D

A

 

Batch execution completed 10 times.

Use AdventureWorks2014GODECLARE @SortCoulmnName VARCHAR(50) = 'OrderDate';DECLARE @SortColumnNumber AS INT SET @SortColumnNumber = CASE When @SortCoulmnName='SalesOrderID' THEN 0 WHEN @SortCoulmnName='OrderDate' THEN 1WHEN @SortCoulmnName='DueDate' THEN 2WHEN @SortCoulmnName='ShipDate' THEN 3 ELSE 0ENDSELECT SalesOrderID,OrderDate,DueDate,ShipDate ,StatusFROMSales.SalesOrderHeaderORDER BY CHOOSE(@SortColumnNumber,SalesOrderID,OrderDate,DueDate,ShipDate) DESCGO