通过IN子句订购SQL Server的结果 [英] Ordering SQL Server results by IN clause

问题描述

我有一个使用IN子句的存储过程。在我的ASP.NET应用程序中，我有一个多行TextBox供给值的存储过程。我希望能够通过值命令，因为他们在文本框中输入了。我发现了如何在MySQL中做到这一点很容易（采用现场功能），而不是SQL Server等价的。

I have a stored procedure which uses the IN clause. In my ASP.NET application, I have a multiline textbox that supplies values to the stored procedure. I want to be able to order by the values as they were entered in the textbox. I found out how to do this easily in mySQL (using FIELD function), but not a SQL Server equivalent.

所以我的查询是这样的：

So my query looks like:

Select * from myTable where item in @item

所以，我会在值传递从我的应用程序，如113113，112112，114114（以任意顺序）。我想订购由该列表的结果。

So I would be passing in values from my application like '113113','112112','114114' (in an arbitrary order). I want to order the results by that list.

请问CASE语句是可行的？我不知道有多少项目在文本框中的数据都来了。

Would a CASE statement be feasible? I wouldn't know how many items are coming in the textbox data.

推荐答案

你是如何parameterising的在条款？

How are you parameterising the IN clause?

由于你是SQL Server 2008上我会通过在两个表值参数列项目和 SORT_ORDER 和加入该网址。然后，你可以仅仅通过SORT_ORDER 添加订单到最后。

As you are on SQL Server 2008 I would pass in a Table Valued Parameter with two columns item and sort_order and join on that instead. Then you can just add an ORDER BY sort_order onto the end.

这篇关于通过IN子句订购SQL Server的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！