你可以在SQL Server准备语句中参数化排序顺序吗？

Question

我有一个使用VB.Net前端与SQL Server 2005后端通过ADO.Net通信的.Net Web系统。基本上，我想要做的是：

Dim command As SqlCommand = New SqlCommand("", connection)
command.CommandText = "SELECT * FROM someTable ORDER BY orderValue @SortOrder"
Dim sortParam As SqlParameter = New SqlParameter("@SortOrder", SqlDbType.varChar, 3)
sortParam.Value = "ASC"
command.Parameters.Add(sortParam)
command.Prepare()
reader = command.ExecuteReader()

通过查询字符串等方式传递排序参数。这段代码会抛出“Incorrect syntax near '@SortOrder'. Statement(s) could not be prepared.”的错误。

这种情况是否可能存在，或者说我有一个非常愚蠢的语法错误没有看到？

（是的，客户端仅运行 .net 2.0，所以基于 LINQ 的解决方案不可行，遗憾。）

谢谢大家！

更新/响应：

好吧，我也是这么想的。感谢大家给予的支持。（为了一些背景，该命令字符串目前正在动态构建，但我们正在将系统转向更多准备好的语句方向，这是我不知道可能存在的边缘情况之一。）

再次感谢！

- Electrons_Ahoy

Answer 1

不，那样行不通。

我能想到两种可能的方法来实现你想做的事情：

1. 动态构建SQL字符串
2. 从查询中返回数据表，然后使用视图进行排序。

---

通过另外6年左右的经验（加上SQL Server版本），我有一种实现这个目标的方法。

DECLARE @SortByIdASC AS INT;
SET @SortByIdASC = 1;
WITH cte AS (
SELECT Id, Foo, Bar
      , ROW_NUMBER() OVER (ORDER BY Id ASC) AS IdSortAsc
      , ROW_NUMBER() OVER (ORDER BY Id DESC) AS IdSortDesc 
   FROM MyTable
)

SELECT Id, Foo, Bar
   FROM cte 
   ORDER BY CASE WHEN @SortByIdASC = 1 THEN IdSortAsc
                 WHEN @SortByIdASC = 2 THEN IdSortDesc 
                 ELSE ''
                 END 
           , Foo, Bar 

这也允许对列进行升序、降序或甚至从给定排序顺序中排除该特定列。

- Stephen Wrighton

---

实现动态SQL字符串时要小心，避免SQL注入。最好的方法是在视图中进行排序或使用case语句中的建议之一。虽然可能会稍微慢一些，但通常并不重要。 - Robert Wagner

Answer 2

虽然可以更新排序中所用的列。请参考以下示例：

declare @fname int,@lname int

select @fname=1,@lname=0

select * from [user]
order by case when @Fname=1 then firstname when @lname=1 then lastname end

我认为你不能使用这种技术来修改排序顺序，但是你至少可以更改你所排序的列。至少当我尝试时，会收到关于SQL 2005语法的投诉。

- Josh

Answer 3

你可以像现有帖子一样进行操作。

declare @firstSortField int, @secondSortField int
set @firstSortField = 1
set @secondSortField = 3

select firstName, lastName, phoneNumber
from customers
order by 
    case @firstSortField when 1 then firstName when 2 then lastName when 3 then phoneNumber else null end, 
    case @secondSortField when 1 then firstName when 2 then lastName when 3 then phoneNumber else null end

- Fabian

---

是的，但他想改变排序顺序而不是字段;-) - JoshBerke

Answer 4

我不认为这是可能的，只有参数值可以被准备。

- Otávio Décio

Answer 5

不行，你需要每次将其构建到SQL查询中，或者如果你有一组子排序命令，请缓存这些命令对象并重复使用。

然而，对于这个问题的简短回答是“不行”。

- Binary Worrier

Answer 6

这正是您所要求的，而且绝不会将用户输入的字符串放在数据库中。

//从查询字符串中获取请求的排序方式
string sortOrderRequest = request["SortOrder"].ToUpper();
string sortParam = "";
if ( sortOrderRequest.Equals("ASC"))
  sortParam = " order by ordervalue ASC ";
else if (sortOrderRequest.Equals("DESC"))
  sortParam = " order by ordervalue DESC ";
Dim command As SqlCommand = New SqlCommand("", connection)
command.CommandText = "SELECT * FROM someTable " + sortParam;
command.Prepare()
reader = command.ExecuteReader()

- anon

Answer 7

可以的。创建两个别名列，SortASC和SortDESC，将它们填充为您想要的内容，然后按照它们进行排序。

-- Testcase setup
DECLARE @OrderASC bit
SET @OrderASC = 0

-- Statement required
SELECT
    Users.*,
    CASE @OrderASC WHEN 1 THEN Users.Alias ELSE null END AS _SortASC,
    CASE @OrderASC WHEN 0 THEN Users.Alias ELSE null END AS _SortDESC
FROM Users
ORDER BY _SortASC, _SortDESC DESC

- sisve

Answer 8

我扩展了被接受的答案，以实现在 SQL 查询中对排序子句进行参数化的有效解决方案。以下是示例代码：

DECLARE @Sorting VARCHAR(200) = 'CreationTime ASC'

SELECT v.*, 
CASE WHEN @Sorting = 'CreationTime ASC' THEN ROW_NUMBER() OVER (ORDER BY v.CreationTime ASC) 
     WHEN @Sorting = 'CreationTime DESC' THEN ROW_NUMBER() OVER (ORDER BY v.CreationTime DESC) 
                                         ELSE ROW_NUMBER() OVER (ORDER BY v.Id ASC) END AS rownum

FROM Vehicles v ORDER BY rownum 

- Dipendu Paul