Note: I’m talking about MS SQL Server 2005/8, it may or may not apply to other dbs.
Security:
You can set rights on the procedure level.
Yes, but you can also set the rights on view / table level.
Stored procedures are always immune to sql injection.
Ad hoc queries are always vulnerable to sql injection.
Wrong.
Stored procedures that take strings as parameters and execute them as sql are open to sql injection.
Parameterized ad hoc queries prevent sql injection.
Performance / Execution Plan:
The execution plans for stored procedures are cached whereas ad hoc queries generate new plans each time they are executed.
Wrong.
If there is an execution plan that fits the query, it will be used. The db doesn’t care if the query was an ad hoc query or a stored procedure.
Stored procedures are not pre compiled, they are compiled at runtime, just like any other query.
There is one thing to note though, if you change a parameter (not the value but for example nvarchar(9) instead of nvarchar(22)) a new plan will be generated.
Source: MSDN1, MSDN2, MSDN3, Blog
Centralization:
With stored procedures you “have it all in one place”
Wrong.
You don’t have it one place, it’s in your application as calling code and in your db as stored procedure. If the procedure changes and requires different parameters the calling code has to change too.
You can reuse stored procedures and only have to optimize them once
While that is true, you have to see the other side too. If multiple applications use a stored procedure you can’t easily change the procedure. You’d have to make sure that you didn’t break any of the other applications.
Optional Parameters:
If you use stored procedures for updates, you have to choose between efficient queries and maintenance.
If you want to go with efficient queries, you’d have to create stored procedures for every use case that changes something. If you have a customer table, the procedures might be ChangeCustomerAddress, ChangeCustomerBillingAddress, ChangeCustomerName etc.
If you favor easier maintenance, you’d just have one big update procedure that takes all values at once.
With Ad Hoc Queries you can have both. It’s easier to maintain and more efficient if you have a single method that builds a query that excludes all unnecessary columns.
Changes:
You can change a stored procedure without redeploying the application
That is partially true.
If you really have to change a query, you’ll likely have to change the application too.
Tuning is another story, you can tune a stored procedure without touching the application. On the other hand, you could also tune the query in isolation and change the query when the application has to be recompiled for other reasons.
