sql server - T-SQL: Filter with Dynamic Comparison Operator (=, <=, >=, ...) -

this question has answer here:

• passing operand sql parameter 3 answers

i'm creating reporting tool user can pick operator , 2 values filter on.

my basic table:

userid     username ------------------------------- 1          user1 2          user2 3          user3 4          user4 5          user5 

the user can choose operator i'd translate this:

option      sql operator ------------------------------ between         column between x , y            column '%' + x + '%' greater    column > x less       column < x equal        column = x not equal    column <> x 

i thinking of similar to:

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

in sense if pass or nothing, still query correctly.

here's tsql i'm playing (** not work *):

declare @bwvalue1 varchar(200) = '2', --between value 1 @bwvalue2 varchar(200) = '4'; --between value 2  select * users (userid between @bwvalue1 , @bwvalue2  or userid != @bwvalue1  or userid = @bwvalue1  or userid < @bwvalue1  or userid > @bwvalue1  or userid '%' + @bwvalue1 + '%'); 

is there way write tsql can correctly evaluate statement no matter operator selected?

* final answer *

here's ended curious:

declare @fn varchar(200) = 'carl',     @op varchar(3) = 'bw',     @bwvalue1 varchar(200) = '978',     @bwvalue2 varchar(200) = '2000'  select * users userfirstname '%' + @fn + '%'         , ((@op = 'eq' , (userid = @bwvalue1))  or (@op = 'neq' , (userid <> @bwvalue1))  or (@op = 'lt' , (userid < @bwvalue1))  or (@op = 'gt' , (userid > @bwvalue1))  or (@op = 'li' , (userid '%' + @bwvalue1 + '%'))  or (@op = 'bw' , (userid between @bwvalue1 , @bwvalue2))) 

dynamic sql not mean cannot parameterize , cache plans. works fine because sql server cannot tell difference. if app concats few sql strings (that not contain dynamic literals!) sql server treat them other query. caches plan. of course, each operator result in different plan. maybe want if query can seek on index way!

so recommend make query static except operator.

if cannot , willing give on sargability, this:

where 0=0  or (operator = '=' , (a = b))  or (operator = '<' , (a < b))  or (operator = '>' , (a > b))  ---... 

exactly 1 of or clauses become "active" @ runtime. still looks readable , maintainable.