The debate on Stored Procedures

An recent article I read discussed the issue of using stored procedures versus straight T-SQL statements. The author threw out some pros and cons but basically asked readers to give their thoughts. So here are mine:

I was brought up in the world of programming to always use stored procedures when accessing a database from a client program. The advantages were clear I was told:

1. The stored procedure was pre-compiled and therefore would run more quickly.
2. The store procedure provided better security through the use of the keyword GRANT.
3. The stored procedure protected against SQL injection attacks.
4. The stored procedure easily allowed me to pass parameters to my T-SQL.

I'm sure there are other advantages but those are all I remember at the present moment. Now, about three years later I am starting to question the "Stored Procedure Law". One of the biggest reasons is something I was little aware of in my earlier programming career called "Configuration Management". You have to get your stored procedures promoted from the development environment to production to insure that your code will work when it calls them. However, if you just write sure simple T-SQL in your code, you do not have to worry about this issue. This can be a huge advantage when the PM comes to you and says "Why is this not working?!!?".

Plus, let me give countering views to the four points I listed above in favor of stored procedures:

1. I am not so sure actually that the store procedure is precompiled. Possibly someone can point me to a link that clears this this issue up for me. I have heard arguments both for and against when it comes to SQL Server. Regardless, when you are running a simple select statement the time that it would save is inconsequential to the performance of your overall application. Hence, this is a moot point.
2. This is not true when .Net security is used correctly, especially when the right user permissions for the process running the application is in concert with Windows Integrated Authentication. Also SQL Server 2005 adds extended functionality to schemas, which can restrict the connecting client application to only access certain tables and perform certain functions.
3. This point seems deceivingly true. But when looked at more thoroughly, if a hacker is able to exploit a poorly written T-SQL statement in your code they will be able to equally do so even if it is contained in a stored procedure. The answer here is to validate all user input and to carefully write all T-SQL statements.
4. This point is true at first but again becomes moot when you learn how to pass parameters to a T-SQL statement in your code.

Well, that is my present opinion on the matter, but I am sure that it will change as time passes. Thanks for reading (my audience of one ;).