Handling different conditions present in table parameter to work as "AND" instead of "OR"

Question

I have a page where user dynamically add search condition(s) to filter out records. I am sending those conditions to stored proc in a TVP. Now i need to write a query which applies those filters work as "AND" instead of "OR"

Consider an example of student table

ID          Name         Marks
----------- ---------    -------
2           bab          65
4           bad          75
6           baf          85

I am getting conditions to Stored proc in TVP as (i am sending Ids for column and operator, but for clarity i used column name and operator in this example)

 Column     Operator    Value
----------- ---------   -------
Name        Contains    a
Marks       >=          75

Now i need to use this TVP to filter out records from student table, in this example the last 2 rows of student table will be displayed to user.

Can anyone help me in writing a query for this, i tried making cross join between student table and TVP, but query is ORing instead of ANDing the conditions in TVP

Sample query:

    DECLARE @tmpCond TABLE
(
    ColumnId SMALLINT,
    OperatorId SMALLINT,
    Value VARCHAR(10)
)

INSERT INTO @tmpCond
        ( ColumnId ,
          OperatorId ,
          Value
        )
VALUES  ( 1,1,'a')
        ,(2,2,'75')

SELECT * FROM dbo.Student A
CROSS JOIN @tmpCond B
WHERE 
    (B.ColumnId = 1 AND B.OperatorId = 1 AND A.NAME LIKE '%'+B.Value+'%')
    OR
    (B.ColumnId = 2 AND B.OperatorId = 2 AND A.Marks >= B.Value)

There is no need for a join anywhere to do what you want. You're probably doing it wrong. Can you show us the code you already have? — Irfy, Apr 30 '12 at 05:53
I may be wrong in my approach of using cross join. If the approach itself is wrong, can u suggest me the correct approach to solve this? — Sandeep Polavarapu, Apr 30 '12 at 05:55
@Irfy: Added sample query of my approach, can u suggest me correct approach? — Sandeep Polavarapu, Apr 30 '12 at 06:03

score 1 · Accepted Answer · answered Apr 30 '12 at 06:09

1

You need to do the following:

Create the preliminary query as a string, missing the where parameters: query = 'select * from student where xxx order by name, marks'. We'll replace xxx with proper contents.
Create the a variable for the query part where_part = ''
Use a cursor to iterate through every row in TVP
For every column, operator, value in TVP, build up a string like name + operator + value + ' and ' and append it to where_part.
After having processed all rows from TVP, remove the last ' and ', in this sense: 'condition and condition and condition ~~and~~ '.
Replace 'xxx' in query with where_part
Run exec(@query) (tsql, other DBs have different syntax). See also this article and google for "dynamic sql in stored procedures ".

answered Apr 30 '12 at 06:09

Irfy

9,323
1
45
67

Thanks irfy for the approach, but the approach u suggested is using cursors and dynamic SQL which affects the query execution time (as there will be no caching of query plan applicable for dynamic SQL). Is there anything we can do to solve this without using dynamic SQL? – Sandeep Polavarapu Apr 30 '12 at 06:12
No, unfortunately not. Your requirement for building a dynamic `where` clause necessitates dynamic SQL queries. But think about it: do you really need a highly performant query with a cached query plan, when the user has to build the query manually and dynamically in the first place? It seems to me that the performance penalty is negligible because the query will be created dynamically again, and again -- you cannot predict and cache a plan for such queries anyway. – Irfy Apr 30 '12 at 06:16
hmm.. I am agreeing with u and marking as answer, thanks again :) – Sandeep Polavarapu Apr 30 '12 at 06:19
If you restrict the number of tables searched in this way to 1-2 tables, and limit the columns of those tables to just a couple, and then limit the available operators severely (so as to match just a few predefined use-cases), you may be able to get away with a (potentially huge) cache of parametrized queries, which you select and parametrize based on TVP contents. I'm thinking of hash-table with tvp contents as key, but this would be rather ugly and overly restrictive. – Irfy Apr 30 '12 at 06:19
See also [when and how query plans are cached and when not](http://www.sommarskog.se/dynamic_sql.html#queryplans). I'll try to elaborate in another answer on an abstract cacheable solution – Irfy Apr 30 '12 at 06:27
I will take care about table design but for limiting the columns we have a restriction of max number of conditions to be 5 – Sandeep Polavarapu Apr 30 '12 at 06:28
I just tried to reformulate my solution with `sp_executesql` but failed when I realized that the number of parameters to `sp_executesql` is dynamic. There is [some mention](http://programming.itags.org/sql/64694/) of "variable statements", but I cannot seem to find syntax for it. I don't think there is any reasonable way to do what you want with proper query plan caching. – Irfy Apr 30 '12 at 06:44
Ok no probs, i will go with dynamic SQL approach. Thanks for ur advice and looking deeply into the problem :) – Sandeep Polavarapu Apr 30 '12 at 06:57

Handling different conditions present in table parameter to work as "AND" instead of "OR"

1 Answers1