I wish to have the ability to store an archive filter criteria (basicly what uses Whereby a Choose query) within the database and I wish to use later this criteria inside a Choose. The scenario is the fact that a filter critera is placed through the user (rarely) which criteria can be used each time data records are retrieved (frequently).

A filter criteria is really a boolean expression for example: (x AND y) OR z. x, y and z are key phrases that may be attached to the data records having a junction table.

I'm able to keep expression as text/xml after which parse it and dynamicly create a sql query each time I wish to retrieve the information, but this appears inefficient.

It is possible to better way?

In case your column selection is static an alternate might be developing a StoredProcedure from that question and storing the StoredProcedure title into some 'QueryMaster' table. That ways you'd be restricting your dynamic query to simply Exec SP_EXECUTESQL <SP_NAME> <Params> and becoming the advantage of put together query for the primary selection.

You are able to Avoid Conversions In Execution Plans By Using sp_executesql Instead of Exec.

Just in case your column selection can also be not static, you might want to create sights rather than saved procs. This could most likely be superior only when your primary table is big or using lot's of joins.

This really is presuming you've many possible filter qualifying criterion and few actual filter criteria. For instance, you will find hundreds of posts user can filter on - however they would most likely be creating 25-30 such combinations and on utilizing it.

For those who have small group of posts to filter on an easy method is always to produce a single universal where clause using Case, IsNull, Collace, etc. See Implementing Dynamic WHERE-Clause in Static SQL for couple of good examples.

I've got a feeling there is no better fix for your problem. You'll have to keep filter criteria (the where clause) somewhere inside a database or perhaps in file. Without notice to carry out a query, you will need to obtain the filter criteria particulars in the file/db and make your query with a couple code.

I do not think there's in whatever way for this - DB systems do not have an element that will help with this particular.

Whether it's leading to efficiency issues, you are able to cache the filter particulars somewhere after reading through them up the very first time.