April 20, 2010 at 8:06 pm
Hi All,
I'm kind of newbie, I have time registered here, but this is my first post. After searching through the forum and found not what I am looking for, I need to ask, I hope people not make fun of me if what I want is easy to do for you :$
I am working in SQL Server 2005 and want to create a table that helps to save filters to use through another tables
for example If I have 3 tables:
CriteriaTable, TableProduces, TableUsers
And I want to store in the CriteriaTable all the filters that I will apply in the TableProduces and TableUsers this filters can be Users IN, userID>#, ProducesName LIKE '', etc all filters I can do in a Where but doing through this table
I don't know if I am explaining =(... Does this sound something logical?
Best Regards,
Nice forum =)!!
thanks in advance
April 21, 2010 at 4:10 am
Why to save filters in separate table. You should be having a strong reason to do so.
You always have a choice to get Criteria from front end in varchar and create dynamic sql and execute it. No need to save criteria.
April 21, 2010 at 6:08 am
It's logical, but a poor choice. In order to implement it, all access to your system would need to be through dynamic tsql, where you build the sql statement. This means you can't really set up procedures and tune them because multiple where clauses could completely change the structure of the query.
Also, you have to have a mechanism for identifying which WHERE clause goes with the appropriate query. Again, this is going to lead to problematic performance because you'll have to tune the searches against your criteria table in order to retrieve the information for searches against the other tables... it's almost like Catch 22.
Why are you trying to do this? What problem are you hoping to solve?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 21, 2010 at 8:46 am
Hi,
Thank you both for your quick response. the scenario is this:
I have customers, these customers has produces and a lot more of information, these customers also has contacts.
Sometimes (almost always) those customers want to filter information deppending on the conctact logged to the system,
for example:
- for contact1
* the customer1 want only to show him 2 produces
* customer3 want to show him just produces started with 'pop'
* Customer4 want to hide produces started with 'pop'
* etc
And the contact has his 'MyCustomersProduces' Report where it shows all Customer's produces, in this report I need to apply this kind of criteria filters, so I need to have a kind of table to make some inner join to filter 'automatically' this produces.
Is there a better way of do this?
PS: This criteria table is a plus, I mean I will have a WHERE clause almost always, this criteria table just apply for a number of special contacts
Best Regards,
April 21, 2010 at 9:01 am
Take my advice with a huge grain of salt, but if I were doing this, I'd make those types of choices default values that I loaded for the client. You may or may not want to keep them in a table, but then you just pass them in as parameters to your stored procedures just like any other parameter. At least that's my take. Dynamic SQL has so many issues, it's best to try to stay away from it as much as possible.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 21, 2010 at 9:35 am
Hi Grant,
But the customers want to change this as soon as light speed, If I send this thought parameters I will need to change my Bussiness layer each time a CustomerCriteria changes, just imagine that what I want to program is the database of facebook, talking about permissions and groups, for example but the Customer in this case will administrate this persmissions and will decide what to see and what not to see to the user but this is kind of creepy here in facebook there are certain especific things you can set up to see or not see, in my case the Customer says "ok I want this user to see notices that contains Televisions stuff but not Radio or not from LG And Sony", How can you do that in a way just take you 2 seconds to change this rules at anytime?
Best Regards,
April 21, 2010 at 10:28 am
If you're saying that the searches are always on everything, every time... you're in trouble. But eveyrone always points to web sites like Facebook or Google and all the flexibility that these things have, but you should read up on how they create that flexibility. It requires major, rigid, and careful programming. It's not just a free for all, even though it looks like that on the surface. Underneath there is serious structure.
If everything is as fluid as you say it is, your criteria table idea is probably going to be even more problematic than I initially thought. You'll probably just want to do nothing but completely dynamic TSQL... Good luck. Memory pressure on that system will be extreme. Code reuse will be near zero... lots of stuff to go wrong that way.
But, as I said before, take what I'm saying with a grain of salt. I'm not sitting in your chair and I don't know what all the requirements are.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 21, 2010 at 10:35 am
I see, thank you for your responses, I appreciate the effort you made, but unfortunately the Database is already created =(.. so they are doing this kind of filters though hardcode at the page, I can not redesign the database but I may be able to add some kind of table plus (criteria table) for this purposes, I will have to think how to solve this,
anyway, thanks a lot 🙂
Best Regards,
April 22, 2010 at 6:06 am
Dynamic SQL is your solution. I witnessed this scenario in a differen way. I had the Where clause as the parameter and I use to build the query depending upon the tables in the where clause using Schema and relations between tables. Ofcourse, the select columns were setteled and hardcoded.
Sometimes you face scenarios that are difficult to explain to others.
April 22, 2010 at 9:07 am
Hi Atif,
I need to find a different solution Dynamic SQL is not what I want, I want another kind of solution even if I need to create a filter table for each main table, Produces (ProducesFiltered) or a view, I am thinking in the best solution for my problem =/..
Thank you for your response
Best Regards!
April 22, 2010 at 10:41 am
yoacer (4/22/2010)
Hi Atif,I need to find a different solution Dynamic SQL is not what I want, I want another kind of solution even if I need to create a filter table for each main table, Produces (ProducesFiltered) or a view, I am thinking in the best solution for my problem =/..
Thank you for your response
Best Regards!
The thing is, I don't see how you can store criteria (different criteria on different fields for different clients) and apply it without getting into dynamic query execution. I think Atif is right. The only other option is to create entire stored procs for multiple individual clients and then have some mechanism within a wrapper proc that figures out, somehow, the appropriate child proc to call... yikes.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 22, 2010 at 12:06 pm
yes, that is actually our Plan B haha.. But I wanted to avoid this, because we need to create a StoreProcedure for each filter, I was though about to create a table where I will store for example All Produces I will need to excluse for certain customer, but imagine all the tables I will need to store... there should be a different solution, meanwhile we will stick with Plan B, a SP for each rule or filter criteria for each Customer
Best Regards !
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply