Should you use Stored Procedures or Ad-Hoc SQL?
This question came up recently in a project. During investigating the issue I found a very good discussion via this Coding Horror blog entry. There is a great link there with a very detailed explanation (and discussion) of issue. (Pro's and contra's)
One of the things you will find in the discussion is that the 'SPs are secure by nature claim' is wrong or at least over judged. Only parameterized Stored Procedures and query's are secure for SQL injection attacks.
Don't use Stored Procedures for granted, look at the specific situation, considering security, maintainability and performance.
Great discussion though!