CODEWITHSUNDEEP
sqldatabasedatabase-design
Pking
Pking

Reputation: 963

Saving record filter criteria in sql queries

I want to be able to store a record filter criteria (basicly what comes after WHERE in a SELECT query) in the database and I want to later use this criteria in a SELECT. The scenario is that a filter critera is set by the user (rarely) and this criteria is used every time data records are retrieved (often).

A filter criteria is a boolean expression such as: (x AND y) OR z. x, y and z are keywords that can be connected to the data records with a junction table.

I can store the expression as text/xml and then parse it and dynamicly construct a sql query every time I want to retrieve the data, but this seems inefficient.

Is there a better way?

Upvotes: 2

Views: 2974

Answers (2)

YetAnotherUser
YetAnotherUser

Reputation: 9356

If your column selection is static an alternative could be creating a StoredProcedure out of that query and storing the StoredProcedure name into some 'QueryMaster' table. That ways you would be limiting your dynamic query to just Exec SP_EXECUTESQL <SP_NAME> <Params> and getting the benefit of compiled query for your main selection.

You can Avoid Conversions In Execution Plans By Using sp_executesql Instead of Exec.

In case your column selection is also not static, you may want to create views instead of stored procs. This would probably be better only if your main table is large or using lot's of joins.

This is assuming you have large number of possible filter criterion and small number of actual filter criteria. For example, there are 100's of columns user can filter on - but they would probably be creating 25-30 such combinations and keep on using it.

If you have limited number of columns to filter on a better way would be to create a single universal where clause using Case, IsNull, Collace, etc. See Implementing Dynamic WHERE-Clause in Static SQL for few examples.

Upvotes: 2

Mehdi Maujood
Mehdi Maujood

Reputation: 400

I have a feeling there's no better solution to this problem. You will have to store the filter criteria (the where clause) somewhere in a database or in file. Whenever you want to execute a query, you'll have to get the filter criteria details from the file/db and build your query using some code.

I don't think there's any way around this - DB systems don't have a feature that can help with this.

If it's causing efficiency issues, you can cache the filter details somewhere after reading them up the first time.

Upvotes: 0

Related Questions