T-SQL: Filter with Dynamic Comparison Operator (=, <=, >=, ...) [duplicate]

Question

I'm creating a reporting tool where the user can pick an operator and 2 values to filter on.

My basic table:

UserID     UserName
-------------------------------
1          User1
2          User2
3          User3
4          User4
5          User5

The user can choose an operator that i'd like to translate like this:

Option      SQL Operator
------------------------------
between         column between x and y
like            column '%' + x + '%'
greater than    column > x
less than       column < x
equal to        column = x
not equal to    column <> x

I was thinking of something similar to:

... column = ISNULL(@parameter, column)

in the sense that if you pass something or nothing, it will still query correctly.

Here's the TSQL I'm PLAYing with (** DOES NOT WORK *):

declare @bwValue1 varchar(200) = '2', --between value 1
@bwValue2 varchar(200) = '4'; --between value 2

select * from users where
(UserID BETWEEN @bwValue1 AND @bwValue2 
OR UserID != @bwValue1 
OR UserID = @bwValue1 
OR UserID < @bwValue1 
OR UserID > @bwValue1 
OR UserID LIKE '%' + @bwValue1 + '%');

IS there a way to write a TSQL that can correctly evaluate the statement no matter which operator is selected?

* final answer *

Here's what I ended up with for anyone that is curious:

declare @fn varchar(200) = 'carl',
    @Op varchar(3) = 'bw',
    @bwValue1 varchar(200) = '978',
    @bwValue2 varchar(200) = '2000'

select * from users where userfirstname like '%' + @fn + '%' 
       AND ((@Op = 'eq' AND (userid = @bwValue1))
 OR (@Op = 'neq' AND (userid <> @bwValue1))
 OR (@Op = 'lt' AND (userid < @bwValue1))
 OR (@Op = 'gt' AND (userid > @bwValue1))
 OR (@Op = 'li' AND (userid like '%' + @bwValue1 + '%'))
 OR (@Op = 'bw' AND (userid between @bwValue1 and @bwValue2)))

Answer 1

Dynamic SQL does not mean you cannot parameterize it and cache the plans. Works just fine because SQL Server cannot tell the difference. If you app concats a few SQL strings (that do not contain dynamic literals!) SQL Server will treat them like any other query. It caches the plan. Of course, each operator will result in a different plan. Maybe that is even what you want if the query can seek on an index that way!

So I recommend you make the query static except for the operator.

If you cannot do that and are willing give up on SARGability, do this:

WHERE 0=0
 OR (Operator = '=' AND (A = B))
 OR (Operator = '<' AND (A < B))
 OR (Operator = '>' AND (A > B))
 ---...

Exactly one of the OR clauses will become "active" at runtime. It still looks readable and maintainable.