There's quite a discussion going on between Frans Bouma and Rob Howard on the benefits (or lack thereof) in using stored procedures in your applications compared to dynamic T-SQL statements. While our current company policy disallows the use of stored procedures, I'm still in favor of using it. Not for everything (like a simple select statement), but under certain circumstances a stored procedure can be quite compelling.
While others discuss the pros and cons, I usually refer to the almighty MSDN, where it says:
The benefits of using stored procedures in SQL Server rather than Transact-SQL programs stored locally on client computers are:
- They allow modular programming.
You can create the procedure once, store it in the database, and call it any number of times in your program. Stored procedures can be created by a person who specializes in database programming, and they can be modified independently of the program source code.
- They allow faster execution.
If the operation requires a large amount of Transact-SQL code or is performed repetitively, stored procedures can be faster than batches of Transact-SQL code. They are parsed and optimized when they are first executed, and a compiled version of the stored procedure remains in memory cache for later use. This means the stored procedure does not need to be reparsed and reoptimized with each use resulting in much faster execution times.
- They can reduce network traffic.
An operation requiring hundreds of lines of Transact-SQL code can be performed through a single statement that executes the code in a procedure, rather than by sending hundreds of lines of code over the network.
- They can be used as a security mechanism.
Users can be granted permission to execute a stored procedure even if they do not have permission to execute the procedure's statements directly.
The mere fact that Rob Howard inadvertedly used the word 'pre-compile' on stored procedures, Frans dismisses most of his other arguments. As always, I prefer the best of both worlds.