Some thoughts on Stored Procedure

I did read that post as well. Me, however, am alot more for using a OR/M then using sp´s. I have expressed my opinion on my blog: http://mattias-jakobsson.net/Item/39/

I don't buy into stored procedures. They are so inflexible and diffucult to maintain as your data schema changes.

My solution is to have your SQL Queries in your code where you call your stored procedure. Having the query their instead of just a stored procedure name makes your code more readable and understandable as to what is going on.

Also, since my queries have business logic in them that is related to the application I thinks it makes more sense to have them in the application than in the database. The database should just store your data and not know anything about your application IMHO.

The reason T-SQL and PL-SQL has not changed in all these years is that it is so hard to upgrade and debug the stored procedures and SQL queries.  

While you can do alot of fancy data manipulation in SQL, it is best to keep SQL simple as a storage and retrieval software.   I suggested limiting database programming to CRUD.  Since business is in continuous change mode, put business logic in the technology that is continuously change, the code.  Much like home remodeling, everything can be remodeled but the foundation remains unchanged. Let the database be foundation of the application.

I don't see how you can argue against stored procedures.  There are drawbacks just as there is with any technology but the advantages outweigh the disadvantages.

Advantage 1: Stored procedures are modular. This is a good thing from a maintenance standpoint. When query trouble arises in your application, you would likely agree that it is much easier to troubleshoot a stored procedure than an embedded query buried within many lines of GUI code.

Advantage 2: Stored procedures are tunable. By having procedures that handle the database work for your interface, you eliminate the need to modify the GUI source code to improve a query's performance. Changes can be made to the stored procedures--in terms of join methods, differing tables, etc.--that are transparent to the front-end interface.

Advantage 3: Client execution requests are more efficient. For example, if an application needs to INSERT a large binary value into an image data column not using a stored procedure, it must convert the binary value to a character string (which doubles its size), and send it to SQL Server. When SQL Server receives it, it then must convert the character value back to the binary format. This is a lot of wasted overhead. A stored procedure eliminates this issue as parameter values stay in the binary format all the way from the application to SQL Server, reducing overhead and boosting performance.

Advantage 4: Cached execution plans that boost performance.  You can't get performance out of sql buried inside your source code.  Any code that is sent over the wire will be smaller when executed as a stored procedure than sending it's content.  So it is also true that a stored procedure will understand the path through the stored proc before execution and cache that for better performance.

Advantage 5: Centralized security, administration, and maintenance.  There is nothing currently in place that can rival the tools built for the database in this area.  Nothing...  It just doesn't exist.

Now...  As a developer I can understand the desire to just stick the sql in the code and move on.  However, this is a bad practice for professional applications and it would lead to chaos on any development team that tried to follow it as a standard.  Leave that for hobbyist.