Dynamic SQL Help

HI !!

We are creating an Ad Hoc query tool for our Voters Profile system. Voters Profile Table has voting history for person. It has columns like : G04 P04 G03 P03 G02 P02 G01 P01 G00 P00 and History

G = general election P = primary election xx = represent year

G04 etc columns has value = "Y" if person voted in that year else its null

If Person A voted in General Election in year 2004 and Primary Election in 2003 then

History Column = G04P03

Now, we need to create a Ad Hoc Query where people ask for:

"Give me a list of People who votes in All 4 general election"

OR

"Give me a list of people who voted 2 times in Primary Election"

In order to do such query what we should do? Can some one help?

Hey Chum!

Please take a look at the following URL.

http://www.sommarskog.se/dyn-search.html#dynsql

Thanx,

Smith

|||

I have read the article and implemented the Dynamic Sql. We are giving users capability of Ad Hoc Query on 800,000+ records. Sometime query takes long time to return results and we have time out issue. OR sometime return results are too many (150,000) records.

We have designed Ad Hoc Query tool with idea of

http://www.aspose.com/Products/Aspose.AdHoc/Demos/

Since it is Dynamci SQL I can't have indexes. Can someone help how to reduce execution time on Dynamic SQL?

Our Dynamic SQL Looks like this

CREATE PROCEDURE TEST

(

@.whereClause varchar(4000)

)

DECLARE @.sql varchar(2000)

SET @.sql = 'SELECT FirstName, LastName, Address, City, State, Zip, County, Township, Phone

FROM VotersProfile

WHERE + ' @.whereClause

"whereClause" is build into C# code based on the columns user select in Tool

For eg @.whereClause is FirstName = 'John' AND 'County = 'Bergen' AND (Gender = 'M' OR Gender = 'F')

Can partitioned view or some other technique can help me? Its like search.. can we you some other search techniques?

Thanks

|||prbly not a performance issue but from a security issue consider using QuoteName(). Check books online for more info.
|||No my code works perfectly fine. I need some advice to improve response time for query.|||check ifthis linkhelps. Note the part about using sp_ExecuteSql instead of using EXEC()
|||

I have been using this article only. Articles states:

sp_executesql there are better odds for the query plan to be reused, because sp_executesql builds a prepared query. But you should observe that it depends on what you parameterize on. If you change table or column each time, there will not be any plan reused.

This is what the case is. So I guess my problem is not Dynamic SQL. My problem is result set and seach on almost 1 million records. How do I implement search on large record set in SQL Server?

|||

Let me give an Example:

How about when there is Join Present. How whould you build Dynamic SQL? Do you need Dynamic SQL or there is a Better Way?
For Eg.
Table1 = Customers
Table2 = Interests
Table3 = Customers_Interests
Table4 = Activities
Table5 = Customers_Activities
Table3 holds Customers Interests.
Table5 holds Customers Activities
Lets say Customers Table has Data: C1, C2, C3, C4
Interests Table has Data: I1, I2, I3, I4
Cusomters_Interests has Data:
C1, I1
C1, I3
C1, I4
C2, I2
C2, I4
C3, I3
Activities Table has Data: A1, A2, A3, A4
Customers_Activities has Data:
C1, A1
C1, A4
C2, A3
C4, A1
Now lets say on my webpage I represent Interests and Activities as CheckBoxes.
Case 1:
I select Interests I1, I3, I4 but No Activities Selected. In this case my query has to join Customers, Interests and Customers_Interests Table
So query will be: Give me list of Customers with Interests I1, I3, I4
Case 2:
If I select Activities A1, A3 and No Interests, query whas to join Customers, Activities and Customers_Activities Table
Case 2:
If I select Interests I1 and Activities A1, in this case Query has to join Customers, Interests, Customers_Interests, Activities and Customers_Activities tables(all tables)
How would you write Query?