November 10, 2010 at 11:30 am
Ok, here's the situation...I have a project to create a search procedure. This search procedure needs to be able to search the db on a number of parameters and those parameters could be a single ID or a list of ID's. The front end guys would like to pass me an XML document outlining the various options available. So it would look a little like this:
<Search>
<IDs>
<!-- Multiple OK -->
</IDs>
<LocationName>
<!-- Multiple OK -->
</LocationName>
<DateCreated>
</DateCreated>
<ManagerID>
<!-- Multiple OK -->
</ManagerID>
<etc..>
</etc..>
</Search>
Are they any best practices in dealing with this situation? Should I load each individual list into its own temp table? Should I use dynamic SQL in some way? There are quite a few options I'm just looking for someone who's had a similar situation and any advice they can give.
November 10, 2010 at 11:47 am
I'd take a multi-step approach to this.
First, I'd break down the XML into a series of sub tables for filtering. Basically a series of #tmps or @tmps.
Next, I'd use a series of rowcounts on those tables to determine which ones needed to be included.
Finally, I'd either create the IF statement of doom to swap between join options, or use a dynamic SQL statement to build out the JOIN statement and link in each table that had been given search mechanics.
If you have a true sample XML and the DDL to the linking tables (and some sample data, by preference) I can probably whip up something in the next day or two. My XML skills are rusty. Then again, someone else may be able to get there faster with the samples, too. 😉
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply