Possible Duplicate:
What are the pros and cons to keeping SQL in Stored Procs versus Code

When should you use a stored procedure (such as in MySQL) instead of writing OO or procedural code (such as in PHP, Ruby or Python) that may execute simple SQL queries and does other processing but performs the same task?

+18  A: 

One way to look at stored procedures is as an interface for your database. Clients call the stored procedure and don't worry about the implementation. As long as the signature doesn't change, the DBA is free to modify the underlying schema without affecting clients.

Use stored procedures when you have functional computations that can't be done in declarative SQL and are best left in the database.

Use stored procedures if you aren't worried about multiple database platforms and will never, ever migrate.

Can you give an example of a functional computation that can't be done in declarative SQL and is best left in the database rather than being done by OO code?
Tim Wardle
Business rules are sometimes so convoluted that you wouldn't want to maintain a piece of unclear (but working) SQL code when you could more easily do it in a procedure.
Allain Lalonde
Nope, I was trying to imagine such a thing but couldn't. (The convoluted code made me shudder.) Maybe another way to say it is "If your DBA prefers SP, use them."
Make that "a conceptual, contract-style interface for your database", and I even more strongly agree. It's hard to argue with the self-evident benefit of encapsulating all your SQL in the database, and expose only contract-interfaces.
le dorfier
In theory, only exposing contract interfaces sounds good however these contracts cannot be easily enforced. I have no mapping between my code and the parameters of a sproc. If a sproc changes I have no easy way to determine the impact on my code! How many different applications call it?
Jim Petkus
@Jim: Considering that a table schema can change at any point...Exactly how does embedding sql in your application prevent those problems from showing up? Not only that, but you have to redeploy every application in the event of a schema update.
Chris Lively
@Chris in many cases, the developer controls the schema, and a good ORM or hand-coded database interface layer mitigates the structure change arguments. Further, database changes are usually driven by changes to the application, so the code needs to be modified extensively anyhow.
Adam N
@Adam: If you have One database tied to One application I'll grant you that. However, when you have more than one application and/or web services etc tied to the same database then things change.
Chris Lively
@Chris, developers controlling schemas is hardly ideal, especially if they don't have a good knowledge of data modeling or relational databases. I find it the other way 'round - applications come and go, but data lives forever.

Imagine you have a database that handles postfix mail adresses, and you want to add an email address from your website's domain.

With a stored procedure, you just have to grant your website mysql user the right to execute the procedure that actually adds the datas on the table, without giving the right to look at postfix tables to your mysql user.

This is more secure.

+1  A: 

IMHO, you should almost always use stored procedures. Not only do they provide another level of abstraction between your data access layer and your data, but they also help with performance optimization due to database server's ability to leverage caching.

SPs do not provide much abstraction, but they do tie you to a specific (version of a) RDBMS.
Rob Williams
spGetOrdersForCustomer("custcode") is not much abstraction over the SQL to accomplish it? What do you consider strong abstraction?
le dorfier
I don't know about MySQL, but MSSQL caches execution plans for ad-hoc queries as well as stored procs, thereby eliminating any performance advantage
I'd agree that the performance benefit is arguable, given the same query strategy (which isn't obvious if you use ORM), but I wouldn't use execution speed as a factor on either side.
le dorfier
Stored Procedures make for harder to maintain code. Using an ORM tool we can immediately see the impact of a table change on our code, find all of the code referencing it and easily apply the change to this code. A simple table change using sprocs can be a maintenance nightmare given enough sprocs.
Jim Petkus
@le dorfier, your example is a perfect example of abstraction - someone calling the stored proc doesn't know what joins are taking place, what where clauses are used to filter (besides the custcode), etc. It's a black box to the consumer. In your argument against it, you defined and presented it.
@fallen888 The same arguments can be made as a benefit for an ORM or hand-coded database abstraction layer in the main code. It's not a specific benefit of Stored Procedures, it's a design pattern.
Adam N
@Jim: "Using an ORM tool we can immediately see the impact of a table change on our code". If you had been calling stored procedures instead of going directly against the tables, you wouldn't even need to check the impact on your code -- there would be none, as the change was hidden behind the stored procedure API.
+4  A: 
  1. When you want the queries maintained by a SQL professional instead of a programmer.

  2. When you want to increase maintainability of your application.

  3. When you have multiple applications that need to use the same queries.

  4. Finally, anytime you have set based logic it's much better to keep that logic in a language (SQL) that was designed explicitly for it.

  5. (case for s'procs) Increased security by limiting access to stored procedures only vs full table access.

EDIT: Just to clarify. #4 wasn't to make a case for using s'procs. Rather, the OP asked whether the data manipulation should be done by regular code versus SQL.

EDIT (@Rob Williams: On the other hand, when the performance really matters..) Rob, as someone who has also done extensive large scale enterprise development, I have found it absolutely necessary to tweak SQL code post release.

It is by far much easier to modify a SQL code to better take advantage of an index or provide some type of query hint, etc, when it is in a stored procedure than it is to modify application code.

I don't care how good your source control system is, making any type of application code change requires full regression testing of the entire application prior to rerelease. Maybe you only changed one little thing, but "Bob" changed something else in the "production" branch without letting anyone know.

Under this situation it is so much easier to make a s'proc change, analyze it for potential breakages, get it past QA, and deploy. Further, s'procs make it infinitely easier to change your entire database under the covers without affecting other applications.

One other thing, some people have mentioned "portability" as a concern. In reality, each DB vendor has enough proprietary extensions in their flavor of SQL that it doesn't matter if you use s'procs or not. If you decide to change DB vendors (which, incidentally, is an extremely rare event) most applications end up needing to be seriously analyzed and modified.

Chris Lively
I agree with #1 and #3.
How does using stored procedures increase maintainability over well designed OO code?
Tim Wardle
#4 doesn't necessarily mean you should use SPs
Allain Lalonde
Stored procedures increase maintainability by reducing potential redundancy and providing services at a lower level.
Cade Roux
I like this answer better than mine. Ups for you.
#2 is simply false. #1,3,4 do not require stored procedures (you can do SQL without stored procedures). #1 means increased costs without justification. #3 can be obtained without SQL, let alone SPs (shared code libraries). Wrong.
Rob Williams
I think that #1 and #2 are contradictory.
#2 is largely not true. There may be cases where it is more maintainable (compared to no real strategy at all) but using the proper techniques you will get more maintainable code without using sprocs.
Jim Petkus
@Rob, d03boy, and Jim: If you want to give a SQL DBA the ability to analyze your sql code then you absolutely want it presented in their tool of choice AND separated from your regular code. This, incidentally, is one incarnation of Separation of Concerns.
Chris Lively
@Kibbee: Actually, they go hand in hand. Would you want someone who is a rock star DBA, but not a programmer trying to figure out your C#, php, or VB code?
Chris Lively
@Jim: Anytime you increase the number of people who can work on your application you increase maintainability.
Chris Lively
@Chris: Crippling your application development by using the DBA's limited tools is not an advantage or a step forward, nor is it even necessary. I will explain in an addition to my answer.
Rob Williams
@Rob: I read your answer and I'm not sure what "limited" tools your using but the latest sql management studio has some great analysis features. Further VS for DB Pro's can also do static code analysis on your s'procs, triggers, etc.
Chris Lively
I've just spent 12 years directly experiencing that your points 1 and 2 are just wrong. Point 1 because you assume that there actually is a DBA on most projects and then that the DBA is actually competent at DML. Point 2 because the code written in most stored procedures still uses development approaches at least 20 years old. The problem is not stored procedures themselves, it's the view that they are a better place to put business logic then OO/Functional environments. Points 3 to 5 make more sense.

I've got a few stored procedures for basic things that don't need to be exposed to anyone else, like basic statistics.

Eg. How many bytes of image data does each user take up, or how many files does each user have.

Of course, this is hardly an enterprise level application, so your milage probably varies.

I have access to the database so its easier to write a simple sql script than to write an app that connects to the db, gets a result set, processes the information and then displays it.

+3  A: 

I would say only when the database can be thought of as your service layer. I wouldn't think of doing such a thing in any scenario I can come up with off the top of my head, but I suspect there's a situation in which it makes sense.

Otherwise, I wouldn't consider using SPs anymore. I've never really been a fan of them and often consider it more of a burden than a benefit. It's just another place to maintain code and keep under source control. :)

Chris Stewart
+5  A: 

You should always use stored procedures - because any database refactoring will have a hard time inventorying the myriad of ad hoc queries against it and knowing when things will get broken. If you have an app with hundreds of ad hoc queries (LINQ, dynamic SQL, parameterized queries, whatever), you'll have a difficult time understanding just what services are consumed by the database users.

The question is what is the division of responsibility between the code in the stored procedure vs. code in the client vs. code in views or calculated columns or any other place code can be.

The choice of where to put code is informed by the architecture of the system and the nature of the interface your database is to provide. If many parts of the system will contact the database directly (as opposed to through an object model) and it is important that these different systems do not need to duplicate work, then more work will be done at the database level. Typically this model is useful when you have multi-modal access to the data: batch jobs in the server itself, external libraries, web sites and rich desktop applications all accessing with libraries that can become out of date. At this point the database protects it's perimeter by defining an access layer of SPs which can change functionality (but not their interface).

If all access goes through an application database library which provides a consistent layer of data services so that no one has to duplicate calculations, it may be less important.

If you want to have access to views for report writing, say, then more code might go into views that the SPs use so that the SPs and views don't need to duplicate code.

Cade Roux
+16  A: 

There are alot of benefits to using stored procedures, but I think many of these concepts have become outdated. Performance of parameterized queries is also excellent. Parameterized queries will be cached, just like stored procedures.

Stored Procedures are best used when the logic needs to be kept close to the database. This typically is for complex sprocs involving multiple queries (ex: complex reporting).

Too often I see business logic embedded in the stored procedure. In my opinion this is a big negative to using sprocs. It is too easy to lose your business logic in the data tier, where it does not belong.

In most cases parameterized queries (or some form of ORM tool which generates SQL) will result in more maintainable and less bug prone code. If the proper precautions are taken for security and performance, I would chose parameterized queries over sprocs in most every case.

Jim Petkus
My name is rotard, and I approve of this message. Sometimes there is a bit of database logic that is easier to perform in T-SQL, and in that case stored procs can be useful, but they can very easily become yet another maintenance headache.
@Jim: Exactly why would your business logic NOT belong right next to the very data they deal with?
Chris Lively
An RDBMS is not equiped to to provide this code in an encapsulated and reusable manner. Some business logic could be stored here, but mixing business logic across tiers makes the code very difficult to support.
Jim Petkus
Jim: a sproc not only encapsulates the logic it is obviously reusable. Exactly what business logic do you see as not fitting into SQL? The only thing I can think of has to do with screen flow; but, obviously, that belongs with the screens...
Chris Lively
Often you see multiple sprocs dealing with the same set of tables. Putting business logic into multiple sprocs does not encapsulate that logic. Also you may have delete business logic that does in the delete sproc and add logic that does with the insert sproc. This logic all belongs together!
Jim Petkus
Also another issue with sprocs is that there is no guarantee that they will be used. If you put business logic here there is nothing stopping another developer from writing their own sproc with different logic. This logic belongs in an entity where all logic is easily viewed and is always enforced.
Jim Petkus
@Jim: But it's only enforced in that one application. If another app comes along that has to interact with the database then you have to make sure the developer branches the previous code. However, what if there is more than one language involved? Like when you are migrating to a new one?
Chris Lively
The place where I work has business logic at the sprocs level. I agree with Jim that it's hard to maintain because because it's easy to forget what is where (not a good visibility). On the other hand, they are easy to use from various database aware application platforms...
Wadih M.

One very significant benefit of SPs (And views) is to simplify control of the data.

By denying all permissions to the tables, but granting permissions to SPs and views you can give different users different read/write abilities.

Also, by forcing entry through SPs you can ensure the integrity of the database.

We have clients who have direct access to the databases and this enables great flexibility for them, but it poses a few problems... 1. They have caused problems in the data more than once
2. We can no longer change tables because they use them directly too

Essentially it comes down to engineering vs hacking. An Engineer would only ever use SPs, a hacker might never bother with them. Well, in my opinion that's the difference :)


Oh, and performance. As well as caching (mentioned by one other here), you get the benefit of pre-compiling the execution plan. Once upon a time this could yield odd results due to "parameter sniffing", but that's no longer an issue.

I've seen the same code execute much faster in SPs.

An Engineer would carefully weigh all the consequences of a tool: SPs have more downsides than upsides. A hacker is a super-engineer. "Cracker" is the proper name for the oft-vilified opposite of an engineer/hacker.
Rob Williams
Thanks for clarifying the hacker/cracker difference, Rob. The misuse of the word "hacker", popularized (apparently) by the media's distaste for the goofy-sounding word "cracker", popularizes this mistake.
Phantom Watson
I would beg to differ. A hacker is someone who sits down and "hacks out code", finding their design as they go. As far as I am aware it is derived from journalism (A journalist being "a hack").
+11  A: 

Stored procedures are generally non-portable, meaning they are specific to a particular RDBMS. As a matter of fact, stored procedures tend to be specific to a particular VERSION of a particular RDBMS.

The development tools for the lifecycle of stored procedures tend to be very limited compared to the tools available for general programming languages/platforms. The tools are lacking in contextual help, in storage of the code, in debugging, in refactoring, etc.

The languages for writing stored procedures tend to be very limited compared to general programming languages/platforms. They tend to be procedural, lack many operations, lack most common APIs, and lack many syntax advances (classes, scope, etc.). This has changed somewhat with the introduction of Java into Oracle and .NET into SQL Server.

So, as a general rule, avoid writing stored procedures; writing your code in a general programming environment is more desirable. Use stored procedures when you need their particular advantages, which mainly means high-performance and/or tightly-isolated data processing. A typical system will then have maybe a stored procedure or two, but definitely not dozens to hundreds.

Best wishes.

EDIT: Clarification...

Please note that I am addressing enterprise-class development in-the-large. If you have a tiny application and a few toy stored procedures, then you can probably ignore everyone's advice. I am assuming that the question is being asked for non-trivial scenarios.

I have dealt with every significant RDBMS over a period of nearly twenty years. I have dealt with databases upto 138 TBs, and individual tables of 8 TBs. I have worked with systems exceeding one thousand SPs. I have converted such databases across major versions and across major vendors. I am an architect, DBA, and just a programmer. If you want the benefit of such experience, then here it is. If not, fair enough.

EDIT: Expounding...

Nearly everything done in a stored procedure can be done by issuing comparable SQL statements from an application, particularly including anonymous procedure blocks (the guts of an SP without the name and permanence). Doing it well can avoid the problems and limitations of stored procedures while still retaining most of the benefits.

However, don't forget that bad code can be written in any language, so it is just as possible to write bad SP code as it is to write bad application code. Indeed, based on history and reports of observations in the wild, it seems even more likely to write bad SP code.

EDIT: @Chris Lively: regarding putting database code where the DBA can apply his tools...

Crippling your application development by using the DBA's limited tools is not an advantage or a step forward, nor is it even necessary.

Besides that, having been a senior DBA/architect for about twenty years, I am not generally impressed with what most DBAs do with database code in the applications that they support. I have mentored a lot of DBAs and programmers regarding database code, so please let me describe what I encourage them to do.

Every DBA should know how to make the database engine show them every SQL statement that is executed, regardless of source (inside or outside the engine), and they should know how to analyze that SQL's performance characteristics. I recommend that every programmer learn to do the same. If you can do this, then it no longer matters where the SQL originated, so Chris' recommendation to put the SQL in a SP is null and void.

If the performance of your system matters, such as when several million customers depend on it every day, then you should be checking the performance of every piece of SQL before it gets deployed to production. I recommend doing so as part of the automated tests that can be run as a part of the automated build for the system.

For example, it is very easy to configure an Ant build script to issue each piece of SQL to the database engine for an execution plan analysis. I like to save each execution plan to a text file and commit it to source control, where I can readily see a history of changes. I also make the build script check the execution plan against some simple criteria to ensure that SQL changes have not altered or compromised the performance.

Likewise, I check all my SQL into source control, and I make it easily available both to my application (for execution) and to my build script (for verification). At a minimum, my build script for the database can recreate the entire structure from scratch, and I often make it capable of loading or transferring data as well.

Obviously, I can handle stored procedures, but they are just one tool among many. It is a mistake (an antipattern) to treat SPs as a Golden Hammer.

On the other hand, when the performance really matters, a stored procedure can often be the best and even the only option. For example, when I redesigned a database recently for a major telecommunications provider, a stored procedure was an essential part of the strategy. I was loading forty thousand data files per day, totaling forty million rows, into a single database table (8 TB) that was growing past two billion rows of current data. A public-facing web site accessed that data via a web service, which required pulling a handful of rows from those two billion within just a few seconds. This was done using Oracle 10g, a custom C application, external tables, some bulk data loading, and a stored procedure. However, most of the database code was still in the C application and the stored procedure handled just one specific, performance-intensive piece.

Finally, please allow me to add that I think that everyone needs to get over the idea that programmers and DBAs have to be separated, or that they do radically different jobs, or that one is inherently more difficult or superior than the other. A database, even a big RDBMS, is just another bit of functionality captured in a component with a published API. It is not rocket science, and it does not have to take long for anyone to learn and master, especially at a minimal level necessary to support most applications. I see no reason why most programmers cannot become decent DBAs, or vice-versa. Perhaps not everyone is willing or able to aspire to become an expert in either general programming or databases, but it certainly seems feasible for most everyone to be able to support themselves in both areas. That is especially true with recently-available tools like Python and SQLite.

Best wishes.

Rob Williams
"stored procedures tend to be specific to a particular VERSION of a particular RDBMS". Huh? MySQL is the only case I can think of; but that's because they are behind the curve.
le dorfier
EVERY database engine that supports stored procedures has released major features over the years; I have used most of them. Once you write an SP of any decent complexity, you are very likely to be tied to a very limited range of versions of that specific RDBMS. Toy SPs needn't apply.
Rob Williams
"The languages for writing stored procedures tend to be very limited"...? They're limited for writing business logic, but for data shaping they're unparalleled. And the latter is what you should be doing in SPs, not the former.
Greg Beech
Languages for writing stored procedures have limited control structures, limited modularity features, limited math functions, limited string manipulation, limited APIs, compared to general purpose languages; likewise regarding the development/debugging environment.
Rob Williams
+3  A: 

Historically, Stored Procedures were a very common part of business applcations.

Stored Procedures work for database-centric processing. They can perform better because they are executing on the database server.

The problem with Stored Procedures include: They are written and maintained separately from the rest of the application. They are not portable. They have difficulty scaling to complex logic. Complex business logic benefits from a richer language. A rich domain model and/or the use of an ORM layer has significantly reduced the use of stored procedures.

Their usage continues to diminish, but don't tell that to your DBA ;-)

Do you have any statistical basis for your assertion that their usage continues to diminish? MySQL just introduced them in Version 5, and as far as I can tell, they seem to be wildly popular.
le dorfier
I think it would be hard to produce statistics to support this, but given the wild growth of ORM tools I think it is hard to dispute this fact. "Use Stored Procedures" has been a mantra for so long that it is easy to be blind to the fact that this rule is slowly becoming obsolete.
Jim Petkus
I find this answer to be overly simplistic. There are a number of valid reasons to still require SPs as a portion of an application architecture. For example, the ability to change database providers in the future is a good reason to implement a SP interface to a business system or site.
+2  A: 

Three more you can add to the consensus list -

  1. To encapsulate database logic in the database
  2. To reduce coupling between procedural and database code
  3. To implement well-defined conceptual interfaces for your persistence layer
le dorfier
+7  A: 
  • A stored procedure (SP) can be executed, tested and optimized independently of your application.
  • You can delegate the task of writing SPs to someone who is an expert in this field.
  • Putting all the SQL on the database server leads to cleaner code (both for reading and architecturally).
  • You may gain performance, in execution speed and a reduction of network trafic.
  • Stored procedures are tightly integrated with the DBMS and can be employed as a security mechanism. They help protect your application from SQL injection attacks.
Seventh Element
SPs are still vulnerable to SQL injection, but less. SP lifecycle is more expensive/harder than for app code. SP expert will be more expensive, so maintenance will be more expensive. SPs might mean cleaner app code, but MUCH uglier database code. Tight integration is bad and expensive.
Rob Williams
Considering we're talking about database code, it's hard to swallow SQL being uglier than anything you'd put anywhere else than in the database, in a stored procedure.
le dorfier
+1  A: 

See for fairly extensive discussion of stored procedures and their use.

Jon Ericson
+1  A: 

Building loops in procedural code to do processing on recordsets can result in terrible performance, compared to doing the same thing with set oriented code.

So regardless of whether you keep the SQL in the application code, or create a stored procedure, you should make sure that you do all necessary processing (aggregation, calculations etc) of the data in the select statements.


It really depends. If you have a DBA involved in your project, or you want to abstract the database structure from your project, or you have an operation that is simpler to perform in T-SQL, go for it!

On the other hand, if you are part of a small team with no dedicated DBA and are designing your database around your application, dealing with stored procs may be a waste of your time.

  • Stored procs generally do not improve performance.
  • Stored procs are yet ANOTHER piece of code that must be updated when your DB schema changes.
  • Stored procs are yet ANOTHER piece of code that must be tested and possibly debugged.
  • T-SQL is a PITA to program in compared to C#
"T-SQL is a PITA to program in compared to C#" is a crutch/excuse I see becoming all too prevalent with developers nowadays. I have little respect for a developer who claims any level of experience yet doesn't have a good understanding of SQL.
It is a simple language but it is surprising powerful. I've written quite sophisticated data handling routines and even C# code generators in T-SQL just because it is one place where there is no resistance to grabbing and manipulating data.
Mark Brittingham
Like I said: "...or you have an operation that is simpler to perform in T-SQL, go for it!" But just because you CAN do something with T-SQL doesn't mean you SHOULD. The same goes for any project anywhere. My experience is that while I can and do use stored procs, I'm usually happier without them
+3  A: 

It would be really interesting to have some mildly scientific poll of how many people use SP, versus how many don't, and then break it down by type of programmer (i.e. freelancer, corporate at mega-corp, etc).

I have ALWAYS used stored procedures - right now I am writing an app using Linq2SQL and am trying really hard NOT to use them for the first time ever - just so I can compare productivity and get a feel for the different way of doing things.

I find Linq surprisingly easy to do the trivial things w/out SP's, but as soon as I have more complicated scenarios I instinctively switch back to an SP. I suspect going forward I will compromise and use straight Linq w/out SP's for run-of-the-mill getting of data from the database(and thus only grant read-only access to the tables), and use SP's whenever I want to update or insert data and keep those SP's locked down.

In my work, I almost always control the front-end and the back-end. I can't imagine if I was only doing the back-end work that I would be happy with many programmers of varying skill levels to just "have at it" with direct access to read/write/delete data from the database.

+2  A: 

There are quite a few good answers given above but there is one missing item:

You should use a stored procedure when you have a set of operations that will always be submitted together. For example, I have a "Check In" procedure that has to check and/or change nearly a dozen different tables. This is a very clear and obvious case where the calls to the database should be wrapped in a stored procedure; I dramatically reduce the number of trips "over the wire" when I call this sproc compared with implementing the logic in C#. This benefit is, of course, in addition to the benefit from having the execution plan for this procedure already in cache when all of these operations are performed.

Someone also asked whether placing a lot of code in stored procedures might risk instability because there isn't a good way of finding all of the sprocs and testing them. I get around that quite handily by using a database interface class containing methods to handle the interaction with each sproc. By tucking this class away in a class library, I can easily test all of the procedures via NUnit. I also gain the benefit of very natural calls using only C# parameters (no Parameters.AddWithValue).

This is made easier by the fact that I've implemented a stored procedure that takes another stored procedure as an argument and generates my C# class automagically.

Mark Brittingham
+1  A: 

Using Stored Procedures and not allowing embedded SQL is a symptom of organizational maturity, IMHO.

There are many benefits of using SPs, but it is a good practice to use them REGARDLESS of all of those benefits.

Beware anyone who tells you something is a "Best Practice" or sign of "Organizational Maturity" without offering any actual reasons. They can be misleading.
Adam N
Ok. Embedded SQL in an application or middle tier is prohibited in my organization, plain and simple.
+1  A: 

Generally, I would consider the best case for Stored Procedures if you've got a complex set of data integrity rules that cannot be maintained using Foreign Keys and Check Constraints, and you want the data in your database to be accessible to multiple applications using different languages or runtimes.

For Example, if you've got a CRM application with a WPF .NET frontend on the salespeople's desk, but the customer data is flowing in from a Java Servlet running on your front facing web server, and the Techs in the server room are pulling provisioning data using a Python script they've hacked together, then Stored Procedures are practically a must have.

If you've got one application, on one platform, reading and writing to a single database which you as developer(s) have control over the structure of, it really comes down to personal preference. In general, I recommend against stored procedures in situations like that because versioning and source code management becomes hellish, but there are certainly others who would disagree.

Adam N
I absolutely agree with you; except on the versioning part. RedGate and other tools are wonderful at schema versioning, comparisons, and deployments.
Chris Lively

Honestly, we avoid them like the plague. Our top concerns when writing web applications with databases are performance and scalability. So, we increasingly work to keep the queries we have the db servers do straightforward and fast (we increasingly even shy away from joins), and then do a lot of the work that you might do in stored procs on the app servers.

It's much easier to add a few more app servers than scale your db server effectively and efficiently, especially if you're using a stateless scripting language like PHP.

IMHO, stored procs don't buy as much as people think - one of the reasons I never dinged MySQL for not having them. It's a feature people -think- they want, but goes so far against the grain of scalability that they're best avoided.


my last project (infact all of my pervious projects) we used stored procedures for all CRUD operations, After Linq2Sql and EF we decided aviod SP and use database just for storing data and integrity. These CRUD operations can do much clean and powerful way using Linq to Sql or EF with proper planning and testing (profiler), good luck, i hate bulk passing parameters in sp ;)

+1  A: 

In my opinion and in opinion of many industry Gurus whom I have read, maintain that SPs should be used as a part of BL and majority of DB operations should be done in them.

SP will always have better performance output. SPs are compiled units and they are parsed, syntactically checked and execution plans already created. In case of arbitrary queries executed from DAL will be parsed and syntax checked every time the query is executed. If prepared statements are not used then a query execution plan creations also comes in play each time. So a new overhead.

Hope this helps,


* Chirag Bhatt :- [email protected]

When you're coding a one-off system, I find stored procs are easier to make quick fixes, which helps with maintenance. You only have one live system, typically one demo, and everything is stored in source control, so it's relatively easy to keep track of everything.

When you're trying to write software that's deployed to multiple systems, I find all of a sudden maintenance of stored procedures becomes a headache because it's too easy to make changes in the field, and you end up with a one-off quick fix in one database (implemented by the local DBA because it was an emergency) and this isn't compatible with the other deployed systems. As I said, a maintenance nightmare.

Another idea - keep core logic out of stored procedures, but allow your customers or local DBAs to write their own stored procs for custom reports, etc.

Scott Whitlock