>> More control over the user - don't see how <<
The user can be limited to how many search terms he can input at one
time. If you use dynamic SQL, they can go wild and strangle the
database with hundreds or thousands of requests. It is also easier
to apply edits to the @search_term_# parameters, such as trimming,
upper or lower casing, replacing characters, etc.
> The user can be limited to how many search terms he can input at one
> time. If you use dynamic SQL, they can go wild and strangle the
> database with hundreds or thousands of requests. It is also easier
> to apply edits to the @search_term_# parameters, such as trimming,
> upper or lower casing, replacing characters, etc.
>
Nope - wrong again.
Using a derivative of the CSV approach to passing in the list of search
criteria for that specific column you can do a) all the triming at once, b)
all the validation at once and c) protect yourself from injection.
You can still limit the number of requests, except it's easier and doesn't
require lot's of hard coding and re-testing when you add another parameter.
Did you not do this in your training? This is fundemental - basic stuff.
I noticed you didn't comment on 'optimisation' - I think you fully realise
the problem there (or perhaps you don't because you've never actually done
real coding on real data volumes)??
--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]