Anatoly Lubarsky Logo
programming, design, integration, games, music

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:

Saturday, June 10, 2006 7:15 AM

Comments

# To use Stored Procedures or not.
A while ago, Frans wrote   Stored Procedures are Bad, M'Kay .  None other than Adam...

6/15/2006 12:41 AM by Bill's House O Insomnia

Login

Subscribe via RSS

Article Categories

.Net Framework
ASP.NET Tips
C# Win32 API
HTML, CSS, Web
Javascript Tips
MSSQL Tips
System
System.Net
WebServices

Archives

(02) January, 2018
(01) June, 2013
(03) March, 2013
(02) February, 2013
(01) July, 2012
(01) April, 2012
(01) September, 2011
(01) August, 2011
(03) May, 2011
(01) December, 2010
(01) November, 2010
(01) October, 2010
(01) June, 2010
(01) May, 2010
(02) March, 2010
(01) January, 2010
(02) December, 2009
(03) September, 2009
(03) August, 2009
(09) July, 2009
(04) June, 2009
(03) May, 2009
(02) April, 2009
(03) March, 2009
(02) February, 2009
(02) January, 2009
(04) December, 2008
(04) November, 2008
(05) October, 2008
(04) September, 2008
(05) August, 2008
(04) July, 2008
(05) June, 2008
(07) May, 2008
(04) April, 2008
(03) March, 2008
(02) February, 2008
(03) January, 2008
(03) December, 2007
(05) November, 2007
(04) October, 2007
(05) September, 2007
(12) August, 2007
(11) July, 2007
(14) June, 2007
(13) May, 2007
(13) April, 2007
(10) March, 2007
(11) February, 2007
(14) January, 2007
(14) December, 2006
(12) November, 2006
(08) October, 2006
(09) September, 2006
(06) August, 2006
(08) July, 2006
(10) June, 2006
(09) May, 2006
(22) April, 2006
(25) March, 2006
(12) February, 2006
(14) January, 2006
(19) December, 2005
(17) November, 2005
(16) October, 2005
(16) September, 2005
(12) August, 2005
(14) July, 2005
(09) June, 2005
(12) May, 2005
(12) April, 2005
(20) March, 2005
(11) February, 2005
(12) January, 2005
(18) December, 2004
(13) November, 2004
(12) October, 2004
(14) September, 2004
(09) August, 2004
(23) July, 2004
(19) June, 2004
(29) May, 2004
(19) April, 2004
(16) March, 2004
(09) February, 2004
(06) January, 2004
(02) December, 2003
(01) November, 2003

Post Categories

.Net and C#
Android
Antispam
App. Development
Architecture
ASP.NET
Blogging
Deprecated Projects
Facebook Platform
Fun
Google
iOS
Javascript
Misc.
MSSQL
Music
My Games
Performance
Roller
Social Networks
Tools
Visual Studio
Web 2.0
WebServices

About Me

linkedin Profile
Recs
Who am I

My Sites

Billy Beet
x2line blogs