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?
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.
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.
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.
When you want the queries maintained by a SQL professional instead of a programmer.
When you want to increase maintainability of your application.
When you have multiple applications that need to use the same queries.
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.
(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.
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.
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. :)
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.
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.
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 :)
EDIT:
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.
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.
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 ;-)
Three more you can add to the consensus list -
See http://stackoverflow.com/questions/59880/are-stored-procedures-more-efficient-in-general-than-inline-statements-on-moder for fairly extensive discussion of stored procedures and their use.
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.
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.
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.
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.
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.
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 ;)
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,
Regards,
* 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.