[RESOLVED] Allow users to build WHERE clauses

[Zvoni]

Do you mean like providing a TextBox the user can enter "WHERE Salary>=2000"?

I'd rather first check, if the way i'd usually do something like this would be feasable (if i ever would get such an order):
Think Mac-Finder-App --> kinda like an Explorer with Miller-Columns

First column: available tables to choose from (if it's always the same table, then you don't need that one)
Next column: available columns
after selecting a column (move it from left ListBox to right Listbox?) offer a Dialogue/TextBox to enter the value, via Dropdown the operator ("=", "<", "LIKE" etc.), and optional the boolean operator for the next one (AND/OR/NOT)

It's just a concept-idea of mine. No Idea if it's practicable.
OTOH, the DMS at the company i work for provides exactly such a dialogue to the user if he's searching a document in the archive

Additionally, you wouldn't really need to think about injection (i take it you'll use parameters anyway)

[szlamany]

Do you mean like providing a TextBox the user can enter "WHERE Salary>=2000"?

I could offer this as an immediate soluion - novice users would fail quickly.

I'd rather first check, if the way i'd usually do something like this would be feasable (if i ever would get such an order):
Think Mac-Finder-App --> kinda like an Explorer with Miller-Columns

First column: available tables to choose from (if it's always the same table, then you don't need that one)
Next column: available columns
after selecting a column (move it from left ListBox to right Listbox?) offer a Dialogue/TextBox to enter the value, via Dropdown the operator ("=", "<", "LIKE" etc.), and optional the boolean operator for the next one (AND/OR/NOT)

I was headed in this direction - select the column, then enter the values. Currently when I do the client-side filtering I already have the data so the dialogue with the user is more clear (like Excel does when you filter a column with NOT SO MANY distinct values!).

Allowing the users to enter the "operator" - that's interesting. In my current client side filtering I only allow = or NOT =...and I force AND's which gets useless if you do some NOT's - looking to enhance this area soon as well.

Additionally, you wouldn't really need to think about injection (i take it you'll use parameters anyway)

I'm in a STORED procedure, building a SQL select statement in code - in the SPROC. There are no parameters at this point.

[Zvoni]

Allowing the users to enter the "operator" - that's interesting. In my current client side filtering I only allow = or NOT =...and I force AND's which gets useless if you do some NOT's - looking to enhance this area soon as well.

I feel you.
I've had users trying to formulate such a WHERE-Clause running into a "deadlock" (at least i call it a deadlock --> basically the one filter excluding the other) returning an empty query.

On a sidenote: Since you mentioned Excel (and its Autofilter-Function):
Instead of "LIKE" you could offer "Starts with" (MyColumn LIKE 'AValue%'), "Ends with" (MyColumn LIKE '%AValue') or "Contains" (LIKE '%AValue%')
Of course you would have to do a sanity check on the value (stripping wildcards if a user thinks he's clever and enters %AValue% in that TextBox)

As for the boolean Operators AND/OR: Yeah

I could never explain why a WHERE SomeValue=1 AND SomeOtherValue=2 OR SomeOtherValue=5 failed for the Users (a.k.a. returning not expected results)
You try to teach them placing paranthesis at the correct position: WHERE SomeValue=1 AND (SomeOtherValue=2 OR SomeOtherValue=5)