|
|
Posted by David Portas on 03/24/06 23:53
Herb Caudill wrote:
> 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).
>
But if you wanted to do more stuff in SQL then you'd put that in a proc
as well.
> 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.
So use parameterized procs that only return exactly what is needed.
There are lots of excellent reasons why we use procs for all things
you've described. Here are some links:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/optimsql/odp_tun_1a_6x45.asp
http://www.sql-server-performance.com/stored_procedures.asp
http://www.sommarskog.se/dynamic_sql.html
http://weblogs.asp.net/rhoward/archive/2003/11/17/38095.aspx
--
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]
|