通过一个操作数作为SQL参数 [英] passing an operand as an sql parameter

问题描述

我目前的工作在具有SQL Server 2008中作为其后端的asp.net应用程序。我想给用户指定他们希望在SQL语句过滤由什么能力。
在界面上，我给他们的选项，选择下列一个下拉：
等于
比...更棒
少于
等等

I am currently working on an asp.net application that has sql server 2008 as its backend. I want to give the user the ability to specify what they want to filter by on the SQL statement. On the interface I am giving them the option to select the following as a dropdown: equals to greater than Less than etc

我想通过这个作为SQL查询参数来执行。我如何最好地实现这一目标？

I want to pass this as a parameter on the sql query to be executed. How best can I achieve this?

有关如;

Select amount, deduction, month from loan where amount @operant 10000;

在 @operand 是上面的下拉是的返回值 =＆LT; ＆GT; ＆LT; = GT =

the @operand is the return values of the above dropdown which is = < > <= >=

推荐答案

假设所有的正整数＆LT; 2十亿，该解决方案避免了多个查询和动态SQL。 OPTION（RECOMPILE）帮助阻止参数嗅探，但这取决于表的大小可能不是必要的，您的参数设置和优化特设工作负荷设置。

Assuming all positive integers < 2 billion, this solution avoids multiple queries and dynamic SQL. OPTION (RECOMPILE) helps thwart parameter sniffing, but this may not be necessary depending on the size of the table, your parameterization settings and your "optimize for ad hoc workload" setting.

WHERE [Amount] BETWEEN 
CASE WHEN @operand LIKE '<%' THEN 0
     WHEN @operand = '>' THEN @operant + 1
     ELSE @operant END
AND
CASE WHEN @operand LIKE '>%' THEN 2147483647
     WHEN @operand = '<' THEN @operant - 1
     ELSE @operant END
OPTION (RECOMPILE);

这篇关于通过一个操作数作为SQL参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！