3

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)))
Losbear
  • 3,255
  • 1
  • 32
  • 28
  • Build a dynamic SQL based on specified parameters. – Yuriy Galanter Jul 23 '13 at 21:19
  • Yea I was trying to avoid having to that. Requirements say I should use "Parameterized SQL" so that sql can cache the sp for future use, execution planning, etc. – Losbear Jul 23 '13 at 21:22
  • I don't think with such multitude of varied parameters SQL can create a normal steady plan that would work good in all scenarios. – Yuriy Galanter Jul 23 '13 at 21:24
  • 1
    You can't parameterize operators like > or <. Only parameter values. Use dynamic SQL. If you turn on Optimize for ad hoc workloads, you don't pay for expensive plan cache bloat except for those variations of the query that have been executed multiple times. – Aaron Bertrand Jul 23 '13 at 21:26

2 Answers2

8

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.

usr
  • 168,620
  • 35
  • 240
  • 369
  • BETWEEN gets a little cumbersome. But this is similar to [my answer here](http://stackoverflow.com/questions/10703072/passing-an-operand-as-an-sql-parameter). – Aaron Bertrand Jul 23 '13 at 21:40
  • you saved my day..it is better to set an AND cond before using OR WHERE col1='aaa' AND ( (Operator = '=' AND (A = B)) OR (Operator = '<' AND (A < B)) OR (Operator = '>' AND (A > B)) – ashraf mohammed Dec 07 '14 at 12:53
0

One cheap way I've accomplished this is by using boolean comparators to specify which comparison I want.

WHERE (A = B AND @equalOp = 1) OR (A > B AND @gtOp = 1) OR (A < B AND @ltOp = 1)

Simple, and doesn't even require dynamic SQL. The query optimizer should prevent the actual comparison from taking place by using the boolean to minimize the query. I would double check this, but it worked swell for me!

Codeman
  • 12,157
  • 10
  • 53
  • 91