CODEWITHSUNDEEP
c#sqlsql-serverdata-miningdata-retrieval
CharithJ
CharithJ

Reputation: 47540

Data Mining/Retrieval Question

Our SQL Server 2005 database has nearly 100 different tables. We are going to provide a feature to search some information according to the user defined filters. Our filter configuration going to be quite complicated. A user can easily create a filter that joins over 20 tables with complicated conditions.

In order to reduce the complexity of the joining, we are planning to create couple of SQL Server views. But that solution itself doesn't seems to be enough to simplify the SQL. We are going to generate the SQL according to the filter configuration. The SQL will be generated dynamically. (I am not sure how long that SQL would be… Is there any maximum length for SqlCommand?) SP is another option...

Have you come across a similar situation? Do you have any good advise/solution for me?

Thank you!

Upvotes: 0

Views: 249

Answers (2)

Mitch Wheat
Mitch Wheat

Reputation: 300579

You should read The Curse and Blessings of Dynamic SQL to be aware of some of the pitfalls.

Upvotes: 1

Brent Stewart
Brent Stewart

Reputation: 1840

I would suggest creating a reporting database that de-normalizes your data, so you don't need the complicated queries. Also make sure that any queries are parameterized to avoid sql injection attacks.

Hope this helps.

Upvotes: 1

Related Questions