Posted by Erland Sommarskog on 03/25/06 00:07

Herb Caudill (herb@caudillweb.com) writes:
> But stored procedures are a dead end - you can't refer to them in any
> other objects. I can't select subsets of the results of a stored
> procedure, I can't join against it, I can't count its results without
> using cursors (not in SQL, anyway).
>
> So if I replace my base view with a stored procedure, I can't do
> anything further with it in SQL, and the client has to do everything.
> The .NET code for each section of the website has to request the entire
> set of content items, and then choose the ones it wants. That's no good
> for two reasons. One, each request is pulling far more data than it
> really needs. Two, SQL is far better at selecting subsets than .NET is.

I can't really get this. If you make your view and then make further
selections from it. What reason do you have to expect that there will
be some inherent sorting into it?

The purpose of TOP n PERCENT ORDER BY in a view is to define a subset
by some criteria - not to specify how the data is created. You've taken
the feature and tied your own definition to it. That usually comes back
and bite you one day.

Maybe you should look into the row_number function. This permits you
to number rows in a query according to some criteria. The final queries
that output data would need to order by this column, but the definition of
the row-number column would have the complex sorting logic.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]