Nice article called To SP or not to SP in SQL Server: an argument for stored procedures by Adam Machanic published on simple talk:
"In my opinion, all external database access should occur through stored procedures. In this article, I review a few of the factors that inform this belief and discuss a way of thinking about the database that is aligned with sound software architectural practices."
The article focuses mostly on the "database-as-API" concept. I agree 100%. Always use stored procedures. It is always good to hear someone that you can feel the experience behind his words. Several months ago I forced myself to unsubscribe from a bunch of "dabase guys" with quite the opposite "opinion".
My additional 2 cents to the points mentioned in the article:
- Performance (here I disagree, since SPs are more performant than ad-hoc queries, even properly used, it is a matter of test, nothing else :))
- Reduced network traffic. Stored procedures can consist of many individual SQL statements but can be executed with a single statement. This allows you to reduce the number and size of calls from the client to the server.
- Faster execution. Stored procedures query plans are kept in memory after the first execution. The code doesnt have to be reparsed and reoptimized on subsequent executions. Even query execution plan is cached JUST THE SAME also for ad-hoc queries which explicitly use RPC, moving a query into a stored procedure can put it into a separate procedural context.
- Programming and Architecture
- Enforced consistency. If users modify data only through stored procedures, problems resulting from ad hoc modifications are eliminated.
- Reduced operator and programmer errors. Because less information is being passed, complex tasks can be executed more easily with less likelihood of SQL errors. Here we talk about eliminating SQL parsing errors (not runtime !).
- Automated complex or sensitive transactions. If all modifications of certain tables take place in stored procedures, you can guarantee integrity on those tables.
- Parameters to stored procs never reflect the partial execution of the stored proc if it encountered an error. (TSQL basics - parameter passing).
Saturday, June 10, 2006 7:15 AM