April 28, 2009 at 12:44 pm
Every so often, I am required to create a web page that searches one or more tables using a set of criteria from which a user can choose. I have done this probably six different times over as many years, and each time it is a painful exercise. And my technique makes any maintenance difficult, such as adding or removing search criteria or changing the result set. I thought I would describe my approach here to see if any of you might have a different, better way. I'm not going to post any SQL or web page code because, frankly, there's too much of it. That's one of the problems.
The web page has the various controls on it; a dropdownlist for accounting periods, an Ajax autocomplete for a customer key, a textbox to enter an accounting unit, and so on. My most recent search page has 14 controls on it. Each control that is a textbox has radio buttons for "Contains" and "Match Exactly". I pass the selected criteria into a stored procedure. The stored procedure needs to search two tables, so it builds two big honkin' nvarchars for sp_executesql that AND all the criteria. If the user chooses "Contains" for any text-based criteria, I build the nvarchar with the appropriate "Like" operators. There is also an option on the page to only search one table or to search both. I insert the resulting rows into a base table that has a sessionid from the web page (users might want to have several search windows open at once, so the sessionid matches the resulting rows to the user for display). If I'm only searching a single table, I can use a local table variable to hold the result set or if the search criteria are few just use a query. I don't need a sessionid because as soon as I send the resultset back to the web page I'm done with it. But if I'm searching more than one table, I have to run sp_executesql for each table and accumulate the results. Then the last statement in the sproc deletes all rows for the given sessionid.
So that's my tale of woe. I don't want to change the web page because the users love the interface. It is very easy to use. Has anyone used a different technique to perform the search? Thanks for any input.
April 29, 2009 at 5:04 pm
Anyone? Anyone? Buehler?
April 30, 2009 at 3:03 am
Wat about the select list in the select statement. Are the columns fixed for one and both table search?
I have developed the sp for an interface with multiple controls on it (may be over 70-80 with rdio buttons, drop downs, text boxes, etc). The Select list was fixed for any search. The logic of the sp was that the front-end developer will send me the where condition and the table list. Now, the table list is made by the developer depending upon the selections made by the user.i.e. each control is bind with one table from where its value is searched.
No comming to the back-end. Depending on the Fixed select list comming out of two main tables of the database, I have already saved all the possible joins upto 5th level in a table. Now, on getting the parameters, I simply go to my table, make the joins (which requires some processing) and make the query. After this I join the where condition, sent as a parameter, with the query. And the I execute it.
Bingo... Here is the result...
Its not that simple. Also, the whorst condition in my scenario is that if the join is not in my table, I have to search the metadata for the joins.
I dont know, if my scenario meets yours, but it is just one time job. After this, all I have to do is to keep my table with joins up-to-date.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply