passing an operand as an sql parameter

asp.net, c#, sql-server-2008

Solution

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);

Problem

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? for eg; ``` Select amount, deduction, month from loan where amount @operant 10000; ``` the `@operand` is the return values of the above dropdown which is `= < > <= >=`

Original source