Always Use Stored Procedures in MSSQL

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).

Related Posts:

• MSSQL 2005 Service Pack 1 is available
• Enterprise Library: Bug with decimal in DataAccess block
• Microsoft Query Optimization Team is Blogging
• MSSQL 2005: Paging with ROW_NUMBER is the fastest
• MSSQL: Managing Schema Changes on Large Tables
• The most important thing in SQL
• T-SQL: Get only Date from DateTime
• T-SQL: Date Ranges
• Date Processing - T-SQL or SQLCLR ?
• MSSQL Dynamic Management Views (DMVs) for indexes

A while ago, Frans wrote   Stored Procedures are Bad, M'Kay .  None other than Adam...