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
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;
is the return values of the above dropdown which is
= < > <= >=
Assuming all positive integers < 2 billion, this solution avoids multiple queries and dynamic SQL.
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);