|
|
Posted by David Portas on 03/25/06 01:02
Herb Caudill wrote:
> The problem is that the criteria for what subset to be return might be
> simple or complicated - but at any rate it's not just a matter of
> passing one or two parameters. For instance, in the past I might have
> had:
>
> view v_ArticlesBase -- in SS2000, provided ordered results
> procedure GetAllArticlesByProgram(@ProgramID)
> procedure GetRecentArticlesByProgram(@ProgramID)
> procedure GetArticlesByArticle(@ArticleID) -- related articles
> procedure GetArticlesByProject(@ProjectID)
>
> .. and so on. At the very least, the ordering logic needs to be
> repeated in each of these stored procedures.
>
> An alternative would be to create a single monstrous procedure that
> consolidates the ordering logic but that uses a lot of conditional paths
> to provide all of the subsets listed above - but that seems really
> inelegant. Some of these are doing simple matches, while others are
> having to traverse one or two linked tables to determine if an article
> should be included in the subset.
>
Well if your logic is as varied as you are making it sound then it's
possibly unlikely that the equivalent SELECTs out of a view would
always respect the TOP 100 PERCENT ORDER BY in a view - even in 2000.
Not unless you use SELECT * in every query perhaps, and we know that
SELECT * is bad practice. Contrary to what you believed, the results in
2000 won't always follow the ORDER BY included in a view.
Also, if you are putting all this logic client-side today then it seems
like you must be constructing a lot of dynamic SQL strings. You said
you are a website developer so I hope you are aware of the SQL
injection issue and what precautions you have to take to prevent
injection from user-supplied parameters. In most cases the simplest and
most reliable solution is to use parameterized procs.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
Navigation:
[Reply to this message]
|