How do I avoid dynamic SQL when using an undetermined number of parameters?
I have a StackOverflow-like tagging system for a database I'm working on. And I'm writing a stored procedure that looks for results based on an undetermined number of tags in a WHERE clause. There could be anywhere between 0 and 10 tags to filter results. So for example the user could be searching for items tagged with 'apple', 'orange', and 'banana' and each result must include all 3 tags. My query is made even more complicated because I'm also dealing with a cross reference table for the tagging, but for the purposes of this question I won't go into that.
I know I can do some string manipulation and feed the exec() function a query to take care of this but I'd rather not for performance problems associated with dynamic SQL. I figure it's best if SQL caches a query plan for the stored proc.
What are some techniques you've used to avoid dynamic SQL in this type of scenario?
By popular demand, here's the query I'm working with:
SELECT ft.[RANK], s.shader_id, s.page_name, s.name, s.description, s.download_count, s.rating, s.price FROM shader s
INNER JOIN FREETEXTTABLE(shader, *, @search_term) AS ft ON s.shader_id = ft.[KEY]
WHERE EXISTS(SELECT tsx.shader_id FROM tag_shader_xref tsx INNER JOIN tag t ON tsx.tag_id = t.tag_id WHERE tsx.shader_id = s.shader_id AND t.tag_name = 'color')
AND EXISTS(SELECT tsx.shader_id FROM tag_shader_xref tsx INNER JOIN tag t ON tsx.tag_id = t.tag_id WHERE tsx.shader_id = s.shader_id AND t.tag_name = 'saturation')
ORDER BY ft.[RANK] DESC
This is functional but hard-coded. You'll see that I have it set to look for the 'color' and 'saturation' tags.
Answer
For an extensive overview concerning this and similar problems see: http://www.sommarskog.se/dyn-search-2005.html
Specific to your question is the part here: http://www.sommarskog.se/dyn-search-2005.html#AND_ISNOTNULL
Also take into account that a (straight) dynamic Solution is not necessarily slower than a (possibly convoluted) static one, as query plans can still get cached: see http://www.sommarskog.se/dyn-search-2005.html#dynsql
So you'll have to carefully test/measure your options against realistic amounts of data, taking into account realistic queries (e.g. searches with one or two parameters might be way more common than searches with ten, etc.)
EDIT: Questioner gave a good reason to optimize this in the comments, hence moving the 'premature' warning a bit out of the way:
The (standard ;) word of warning applies, though: This smells a lot like premature optimization! - Are you sure this sproc will get called that often that using dynamic SQL will be significantly slower (that is, compared to other stuff going on in your app)?