Stack OverflowAre the days of the stored procedure numbered?
[+80] [33] Peter Walke
[2008-10-19 15:49:24]
[ linq-to-sql stored-procedures dynamic-data pragmatic-programming ]

In Scott Hanselman's interview of the stack overflow guys [1], Scott and Jeff make mention that they may be seen as heretics for declaring that the days of the stored procedure are dead. Why might they say this? Is it because many of the new DAL techniques of dynamic data and Linq to SQL use T-SQL to communicate with the SQL server (SQL 2005 in their case)?

Isn't there still significant value in having the T-SQL stored and compiled on the SQL server, along with the benefits of the query plans being cached on the DB? Is it just that these benefits are outweighed by the ease of data access with the aforementioned technologies?

I believe you can use stored procedures in Linq to SQL, right? - DOK
(1) Yes, you can use stored procs in Linq to SQL, but in normal use, most people use parameterized sql. - Peter Walke
(2) LINQ to SQL is officially dead:… - RoadWarrior
I've also read that Linq to SQL is dead, but I think my question is still relevant now that MS is focusing more on the entity framework. - Peter Walke
(1) I think its just a case of people questioning age old practices and thinking they could be done better, however, stored procs are here to stay. It makes sense to combine data manipulation code where the data sits. - Vince Panuccio
(3) Fixing a major business critical issue just by modifying a stored procedure..........Priceless!! - Darknight
[+82] [2008-10-19 16:06:24] Milan Babuškov

Stored procedures or not, your business logic needs to be stored somewhere. If you access everything from a single uniform application, then it obviously does not matter. However, if you develop something like ERP system, you might have 20 different applications written in 20 different programming languages and libraries and running on 2-3 different operating systems. If you want to maintain a consistent business logic in that kind of system, you need to keep it all in a single place. IMHO, it's easiest to do it with stored procedures, but other solutions like 3-tier application and SOAP are also viable.

All those contra-procedure arguments are given by people who have obviously never been involved in development of a large system. By large, I mean 500+ database tables and 20+ programmers involved. If you let anyone get away with dynamic scripts it becomes a maintenance nightmare in few years.

(1) What about applications that will never be that large of scale? Does it make sense then to be more pragmatic and use stored procs only when necessary? E.G. use stored procs as the sql equivilent of assembly? - Peter Walke
(2) It takes as long as a few years? Maybe a few man-years... - Jonathan Leffler
(15) I think it is a misconception that the alternative to stored procedures is 'dynamic scripts'. It gives visions of SQL scattered randomly around the system like many classic ASP systems I've seen. This is not what people are talking about when discussing stored procedure alternative. - Craig
(2) I agree strongly with Milan's comment. Even for smaller systems, you need to be VERY sure that your database won't end up being a shared system before you abandon stored procedures. - Gregory Higley
(2) @Craig: Actually, it is exactly what they are discussing. That's what linq is and it is exactly how most non-s'proc systems are built. - Chris Lively
You should be using an OODB like Gemstone for those systems, not continue writing ten times more code than needed - Stephan Eggermont
What you wrote as "you" means DBA. Applications can be developed by 3rd parties, subcontractors, etc. You can't force everyone to use OODB layer. - Milan Babuškov
Milan: you make it sound like Gemstone sits on an RDBMS; OODB is an alternative to RDBMS. If you don't have an RDBMS, then by default everybody is forced to use the OODB! I've seen it done on large programs (hundreds of object types, dozens of programmers). - Ken
@Chris Lively: your comment proves you completely misunderstand, or perhaps don't want to understand, alternatives to stored procedures. - Ashley Henderson
(8) Now in my experience this is the opposite way around. Stored procedures are fine in small systems but one you get to a certain scale they become very fragile (IMHO). There is simply more flexibility in a service layer where you can deal with things like versioning of the interface and more finely grained permissions structures. I guess for me SPs become problematic when they have to do anything other than simple lifting of data. - Chris Meek
(2) Strongly disagree that you want your business logic in stored procedures. - kekekela
Why on earth would somebody share a database. This is why god invented SOA, you share a service or a system. Don't care how it works. - Kugel
Im only a few years late here but will comment because this got 80+ votes. Stored procs leads to business logic in sql. This is very difficult to unit test, hence why CRUD operations can easily be done in an ORM and anything else beyond that can 'usually' be quite helped by doing this in unit tested code. I think that's where people miss this the most. This isn't a case of stuffing sql code into c#, its a case of the ORM handling CRUD operations and the logic stays easily testable by being in code. - Adam Tuliper
[+69] [2008-10-19 22:42:25] Gregory Higley

I disagree strongly with the notion that stored procedures should be abandoned. (And the notion that T-SQL is somehow "arcane".) Whether or not you use stored procedures depends on the role your database plays.

At StackOverflow, the front-end application and the database are tightly coupled. Perhaps not literally, but the point is that the database was made specifically for the front end application.

This is not always the case. I've worked in various corporate scenarios where the database was exactly of the sort described by E. F. Codd [1]: a "large shared data bank". Many, many, MANY applications talk to this database, not just one or two.

In such a situation stored procedures (and excellent check constraints with a dash of triggers) are critical to maintaining data integrity, by providing a consistent interface to the data.

I've heard some say that "business logic should not be in the database." (Although I may be misunderstanding what's meant by "business logic".) Well, that works great if the relationship between your database and your application is 1:1. But again, if your database services a great many applications it's very important that you provide a consistent interface to the data through stored procedures. Some kind of "middle-tier" layer is not enough. Databases tend to outlast the technologies that are used to access them. If you rely on your middle tier to provide the interface to your database, you'll end up rewriting long before your database is obsolete. (And the applications that use the old middle tier may still be around.) That said, there are other reasons to have a middle tier.

Most people use databases as if they consisted of a bunch of dumb hashtables and arrays. This is where the mentality of abandoning stored procedures (and eschewing check constraints and good candidate keys) comes from. They don't see the database as a full player in the application: It's just a place to dump data.

My applications tend to stand things on their head compared to what other developers do. My databases tend to be very "smart". Most of the logic lies in the stored procedures; the middle tier and application code is very light. Many developers think this is crazy, but it allows me to develop new applications atop the database very rapidly, in whatever language I choose. It provides superb data integrity and much fewer side effects when multiple applications are accessing the same shared data store. And I've never had any lasting performance problems, beyond those that naturally occur from time to time in the course of development and are fixed. My corporate customers have been extremely happy.

However, I readily admit that the arrangement I just described may not be appropriate for an app like StackOverflow. It depends on whether your database is a "shared data bank" or not.

Just my slightly rambling 2¢.


I think what you are demonstrating is that there are many types of applications and many different situations. Some situations stored procs make sense, some they do not. For something like I wouldn't use stored procs. But for enterprise apps they probably make sense. - Craig
You're exactly right. I happen to love writing stored procedures because I tend to think in terms of "set operations". So I use them with all applications. My slightly rambling post was meant to argue against the abandonment of them, but not say that the should be used everywhere at all times. - Gregory Higley
The OO guys have shown twenty years ago that that approach doesn't work. The large amount of faulty data in all larger RDBMS's shows it perfectly well. - Stephan Eggermont
Which approach doesn't work, Stephan? I'm not sure I understand you. - Gregory Higley
(1) Stephan: the problem is that it's the "OO guys" that are designing the database; and they're probably having a difficult time escaping the OO mentality. The large amount of faulty data in "all" larger RDBMS is just an illustration of how many poorly designed databases there are - likely due to the aforementioned reason. - Duke
(2) Unfortunately, when creating a new app it's frequently difficult to tell if its data store will become part of a larger ecosystem of products. Better to stick with a consistent, encapsulated interface based off of stored procedures from the outset, and save yourself a rewrite later on. - David Lively
(3) A lot of people defend stored procedures with an argument of database sharing. You should share a service not a dabase. - Kugel
[+20] [2008-10-19 15:56:52] Steve Horn

These two articles describe most of the common arguments against stored procedures.

Jeff Atwood [1]

Frans Bouma [2]


It's interesting that Jeff's statement came out in 2004, before dynamic data or Linq to SQL existed, but his arguments are still (more-so) valid even today as these technologies enable us to be more agile. - Peter Walke
(3) Frans Bouma writes and markets a tool to generate data access layers without SPs, so you can hardly expect him to give a fair or unbiased opinion on the matter. - Greg Beech
(3) @Greg Beech: That seems like an unfair thing to say. Frans Bouma makes and markets LLBLGen, which supports Stored Procedures well. Frans himself has said, "Approaching the matter with "All procs have to go" is not the way to go because it's not true. As you said, sometimes procs are better, which is also the reason why you can call procs from llblgen pro." And, imo, the linked article is not inflammatory and while it doesn't detail the pros of sprocs, it hardly smacks of anti-sproc fanboi-ism. - qes
[+12] [2008-10-19 16:27:19] Egwor

Ugh. I feel that the entire question is loaded.

Sticking business logic into a stored proc, is widely agreed as being a bad idea. I'm sure there are probably cases where it makes sense (perhaps the system is only a db, and doesn't have a gui or anything else?), but I'm not convinced I can think of any!

I think that anyone saying "stored procs are always bad" is not really understanding the (not so) subtle differences between stored procedures and inline SQL. There are benefits, but what you need to work out is whether those benefits have any real consequence to what your functional and non functional requirements are. If they don't, then do whatever is easiest.

One thing I do agree with is that it makes releasing software easier... since schemas tend not to change frequently, but code does. Often someone forgets to release a stored procedure change or trigger change.

One thing I think many people forget is that without triggers and referential integrity you open yourself up to the potential issue that someone in your support team accidentally forgets to update that other, new table or puts a typo in. Triggers, can in some cases help. (Don't forget that triggers are really stored procedures with a bit of a twist, so we ought to include these in the discussions).

I disagree with Jeff's article. His point that 'However, there's one small problem: none of these things are true in practice.' is something easy to feel is incorrect. In the average case he may be correct, but as you start pushing the limits of the technology then you start running into scenarios where the differences between the two have real impact. To mention a few: performance, expected explain plan and refactoring.

  • You know what depends on your tables, so you know what to refactor. Ever tried searching through code to work out what tables your different applications depend upon? In the better dbs you can always do a 'depends' to help work out the consequences.
  • The security argument is is useful, since you know that app A which comes directly to your DB (which happens in real life) is actually only accessing tables x, y and z via stored procedures x and y. Perhaps when we come back in ten years and the system is still running and you have to migrate it to the latest version or decommision it then you'll see how important this is.

One point I'd like to also rubbish is the argument that only x of our developers can debug a stored procedure. Woooah! Hang on, are you suggesting that you have developers writing SQL who aren't able to write a stored proc? That sounds really worrying to me, since they're pretty similar. What else about the database do these developers not know?

At the end of the day, keep data constraints in your database, and put your business logic somewhere else. The argument between sp's and written sql (I'm not talking about dynamic sql) has to be taken on a case by case basis.

I discussed the problems that adding .NET languages to the new SQL server would have with someone from MS and they agreed that the business logic shouldn't be in the server, and that wasn't something they were really encouraging with adding .NET in. :/

(1) "Sticking business logic into a stored proc, is widely agreed as being a bad idea. I'm sure there are probably cases where it makes sense, but I'm not convinced I can think of any!" Hugely subjective statement without any citation. - Guy
(1) I'm not sure this is thought to be a subjective area anymore. Let's remember that data logic (i.e. constraints)!= business logic. is a good example Guy, do you have reason to disagree? If so, what circumstances? - Egwor
"...with someone from MS and they agreed .. that wasn't something they were really encouraging with adding .NET in" --- they still did it though, and that won't stop devs from using it like that :( - gbjbaanb
if I give you a rope, you can commit suicide ;-) - Egwor
[+11] [2008-10-19 16:09:53] Mitch

More than just query plans, having stored procedures compiled and stored on sql server means another layer of implementation abstraction. With technologies like LINQ, the access and munipulation of data is now strongly coupled with business logic. Stored procedures, just like methods, provide an interface to the data. How the stored procedure is implemented doesn't matter to the caller and the implementation can be changed without affecting the callers.

"How the stored procedure is implemented doesn't matter to the caller and the implementation can be changed without affecting the callers." In my experience this is often wrong. Changing the stored proc will commonly break the calling system. - Craig
(3) It depends on how you change it, Craig. If you change the parameters or the data returned, then yes, sometimes it does break the caller. However, internal implementation changes (e.g., for performance, etc.) usually don't break the caller. - Gregory Higley
[+10] [2008-10-19 18:04:28] SquareCog

Folks keep thinking of databases as massive single-node supermachines. For good reason -- that's what they are in the majority of cases.

But there is such a beast as a distributed database, used for handling extremely large datasets. Such systems take advantage of massive parallelism offered by the dozens (or hundreds) of nodes that compose the system. If you tell them what you want to do with the data, they may well know how to distribute the computation, giving you significant time savings.

See, for example, Greenplum's and AsterData's offerings of in-database Map/Reduce.

If you have a single application, a single database, and a couple of developers -- do whatever makes you happy, it doesn't really matter. Push the computation to whichever machine is likely to have more CPU cycles.

If you have a large system that is used by multiple applications, perhaps not all of them on the same platform and using the same language, and some of them not even written by you but rather commercial BI tools -- save yourself the headache, and do implement your business logic in one place.

If you have a large data warehouse that juggles data as if it was auditioning for Cirque du Soleil -- don't go thinking you are going to do better with your "learn to juggle in 7 days" pamphlet.

(1) Up-vote for that last sentence alone :-) - RoadWarrior
Yeah, last sentence is awesome. I'm going to have to steal that one. - Adam Jaskiewicz
When your dataset is large (and maybe distributed) it is indeed better to bring the code to the data than the bring the data to your code. - tuinstoel
That's possibly the greatest last sentence ever. - David Lively
[+7] [2008-10-19 15:58:39] Mendelt

The anti-stored-proc movement has some merit. I've seen lot's of projects go wrong because they tried to put business logic inside the database. I've seen this cause big maintanance nightmares.

In a lot of places, for example when you have multiple programs that use one database, it's a good idea to have a thin abstraction layer between the database and the applications that use it. I think views and stored procedures still have a role to play here. Although we might see products like astoria (service layer for databases) take over this role in SOA architectures.

edit in response to Revolucent
I can name three reasons off the top of my head:
If your business logic get's more complicated than simple validation I found that modern OO languages are more suitable than a set-based procedural language like SQL. I've seen the addition of one employee-type cause changes in more than a hundred stored procs. A well designed business object model is more suited to keep changes like his localized.
For larger databases that are accessed by more than one application usually not every application has the same business logic. Using one set of stored procs with business logic can quicly lead to situations where changes for one application break the other applications.
You don't always want to do a database round-trip just to execute business logic. Stored proc's aren't really portable, you can only execute them in the database. I found that in situations where business logic is implemented in the database eventually you'll start duplicating it in code to minimize database round-trips this causes maintenance problems because of duplicate code. I think it's better to keep it out of the database from the beginning.

(1) How does putting business logic in a stored procedure cause maintenance nightmares? In my experience, it's been exactly the opposite. - Gregory Higley
[+7] [2008-10-19 16:02:02] Adam N

Well, there's always those of us who don't use .NET, and we'll probably keep using Stored Procedures. Also, I'm no expert in Linq (I've never even actually used it), but I suspect there's still a place for Stored Procedures in optimization. In many cases its much faster to run a batch on the SQL server than it is to pull back a larger tableset and process locally.

Finally, as Mendelt posted, sprocs provide a very good architecture-neutral abstraction layer over the underlying data store.

FYI, you can call sprocs in Linq. - DOK
(2) Calling a stored procedure from Linq still requires a stored procedure to exist... - Adam N
[+6] [2008-10-19 21:37:41] Ryan Riley

Stored procedures are by no means dead. I've been using LINQ to SQL in a recent project, and it does a lot of really nice things. For one, it creates parameterized SQL, and the query paths are usually pretty nice. I'd prefer the data load options to create one query instead of one join and several additional queries, but I can't say there's really a performance difference as I haven't looked and I doubt it's significant.

You can still command LINQ to use stored procedures, and this is very beneficial in the one area in which LINQ is missing functionality: batch processing. LINQ is great for general CRUD procedures, but it doesn't perform batch updates (at least in code) the way SQL would. I extended the List.ForEach() method to IEnumerable to give the impression of a batch update, but that is really just syntactic sugar for foreach.

I would really prefer to see LINQ evolve into something like Tutorial-D [1] (e.g. Dataphor [2], rel [3], and Dee [4]). At that point, I would probably agree that stored procedures would be dead, but only so long as LINQ was the only remaining option. Many are familiar with stored procedures, and they'll be around for a long time to come.


Tutorial-D is really cool. - Gregory Higley
[+6] [2009-05-22 16:10:46] tmeisenh

In a not so humble declaration, stored procedures are not dead. I think what is dying is the ability for the average developer to write stored procedures. The decline of programmers who know how to write stored procedures and the decline of programmers who then know when to use stored procedures is a different topic than this one.

[+4] [2009-01-01 20:33:21] RogerV

Realizing that Microsoft is steadily moving in this direction of thoroughly bastardizing the middle-tier layer in respect to the sql relational store of SQL Server, I none-the-less like the purity of putting database performance tuned code into stored procedures.

First of all, there are cases of being able to write code that resides in the database that is sometimes many factors or even an order of magnitude faster than middle-tier code attempting to achieve the same end result. Hence with databases, it often pays to have code that lives very close to the data structures (i.e., the tables and their relations) being manipulated. So from a performance perspective alone, a rationale for something like stored procedures is well justified.

So then the question might become:

  1. Should some standardized database language be used for that? (Something like Oracle's PL/SQL language.)

  2. Or should various ways to do database code in the middle-tier languages be devised such that the code can be downloaded for execution into the database server. (Something like C# .NET LINQ code.)

The thing about PL/SQL is that it remains strictly in the database and is easy to be invoked from practically any middle-tier implementation language that might be in use. This facilitates multiple applications accessing the database (such as legacy apps and then newer, lately-coded green field apps, which is a very typical scenario for any shop).

My personal preference is to not blend middle-tier implementation languages with database so tightly as Microsoft is doing. From a marketing perspective this makes sense for them as they are promoting a vertical stack that is extremely tightly coupled. Take their approach (which is unique to their programming languages in combination to their SQL Server), and you're definitely highly captive to the entire Microsoft stack. Sure, plenty of folks are comfortable with that. Plenty other folks are not.

well, MS have a track record of not quite knowing what they're doing with DB access technology - how many DB access tech can you name that MS has developed over the years? ODBC, OLEDB, ADO, DAO, RDO... they'll come up with more, I doubt they'll ever finish. - gbjbaanb
Whereas Oracle's PL/SQL has been around since, what early to mid 1990s, is mature and rock solid, and is not going way anytime in the foreseeable future. We use a stack of Oracle 10g, JDBC, iBATIS. iBATIS does our data-mapping but lets us live close the nature of the database. Is a very solid stack. - RogerV
[+2] [2009-08-20 07:25:56] too much php

My first boss told me how when he was young he was part of a team developing software for ATMs, and as a final step before deployment, his team had to prove to an external auditor that:

  1. It was impossible to insert transactions with a negative dollar value
  2. It was impossible to insert transactions with a timestamp other than 'right now'.

They had the database privileges configured so that the only way for the software to add transactions was through stored procedures which guaranteed both of those constraints would be met. The audit was passed without even looking at the software's source code.

[+2] [2008-10-19 22:10:59] Guy

I would argue that there needs to be a distinction between what is "business logic" and what is "data logic".

It is obvious that any operation that manipulates a significant amount of data over a series of operations, especially under a "two phase commit" scenario, should be performed as close to the data as possible. SP's are a no brainer in this case.

It is obvious, that the application layer should not try to replicate or mimic the built in data management systems of a RDBMS, i.e. constraints and joins etc.

Now, the steps performed to close a bank account (calculate interest, calculate balance, transfer balance to a different account, print the closing statement, mark the account as close for example), could be performed by a single call to a stored procedure, or via a series of "high level" sp calls to the database (and maybe other databases). The order of the calls, to which database, and the consequences of each call is defined by the business (the analyst) and is therefore defined by "business logic".

At this high level, then there is no difference between a .NET application calling a single SP or a few SP's.

If the requirement is to change the data, then it should be in a stored procedure. This is "data logic" (your implementation).

If the requirement is to modify the actions and behaviour of the application, then this is "business logic" (the purpose of the application).

[+1] [2009-01-01 19:45:12] barneytron

From what I have observed at work (which is a Microsoft shop), the side of the sproc vs dynamnic-sql fence our developers land seem to depend on when they started honing their craft. The ones who started back in the 90's always go with sprocs, while those who started during the .NET era favor dynamic sql and ORMs. My reasoning for this is that MS promoted sprocs in the 90's, and now with .NET, MS is promoting dynamic SQL through LINQ. Personally, I think either approach can do the job and that the preference of one technology over the other oftentimes has to do with the developers' age.

I've gone exactly the opposite direction from what you describe. I used to be anti-sproc in the days I worked with SQL Server. However, I've been at an Oracle shop for over five years and I've gained an immense amount of respect for what can be done in PL/SQL layer. My stack is Oracle/JDBC/iBATIS. - RogerV
I don't think it's an old vs new developer argument. There are definitely some advantages to having the stored proc code live on the DB server, but with all the tools that are now provided to us to make dev faster, the benefits might be outweighed by ease of coding. - Peter Walke
Sorry I didn't answer to your liking. It's what I see at my work. And I still think that newer (younger) devs will always have more techniques to choose from, while the elders tend to go with what has worked in the past. You can't teach old dogs new tricks, and developers aren't any different. - barneytron
Back with MS-SQL 7.0 sprocs were faster than parametrized query CRUDs, and unfortunately that is still be long in offices by DBA(young and old). It was a MS-SQL thing, and saying that stuff in an Oracle environment would get you laughed at. - Will Dieterich
Thanks for your point-of-view Will. I just hope when developers upgrade their old tools that they learn the new versions with a clean slate in their heads. Hope I didn't come off as ageist; I'm not exactly young myself. I just see too many older developers hanging on to old thoughts. - barneytron
[+1] [2010-08-31 22:09:55] Dale Wilbanks

Performance: That's the #1 reason I continue to rely on stored procedures, and it is not about execution plans or pre-compiling. The bottom line, is that for a well written application, the #1 performance issue is I/O in and out of the database. The less you pull out of a database and then stick back in, the better the performance.

For example, if I had a table with a "Cost" column, and needed to make Cost = Cost * 1.5 for 10,000 records; my sproc could do that in a couple of seconds, and data would never leave the database. If my app needs to pull each record, change the value, then save the record back 10,0000 TIMES, then how long will that take? What about dependent data (Joins), which would make the non-sproc approach even worse.

When an ORM can do that update without going into a 10,000 step loop of some sort, let me know, I'll be interested.

The more I read about EF and Link2Sql approaches, and how they emit Sql that does those joins, the more intrigued I am. I see benefits now for using Linq2Sql approach for SELECTing data, but cannot trust an ORM for persistence (yet). jqGrid using Linq2Sql rocks for filtering data, very impressed. - Dale Wilbanks
[0] [2009-02-22 23:25:41] Andy Dent

I think the days of the stored procedure written in a variant of SQL different from the application programming languages may indeed be numbered.

I think the future is in stored procedures written in the application programming language, with some SQL commands, that can be migrated to SPs and back to app code with virtually no change. It would then be a security and tuning decision as to where logic executes.

SQL Server 2005 made a big step in this direction with its server-side CLR [1] procedures and it will be interesting to see how this goes, for example will the DLR mean that IronRuby will be available in SPs?

Java is available for stored procedures in Oracle [2], DB2 [3] and c-tree SQL Server Java Edition [4] and has a lame Wikipedia article [5].


You can use server-side CLR procedures with Oracle too but the database has to run on a windows machine, not a Linux or Unix machine. - tuinstoel
[0] [2009-02-22 23:37:55] erikkallen

I don't know. When it comes to SQL server, I think it hardly matters. T-SQL is too limited, and SQL CLR doesn't really offer any major advantage over code inside the application, IMO. What I do like is Oracle's PL/SQL, where all queries are statically typed, so you can easily se what you break when changing a table.

[0] [2009-08-20 22:56:08] community_owned

Stored procedures will last as long as relational databases.

Do you think that the use of stored procs is limited to relational databases? A non-relational database like MongoDB also has stored procs (they are called stored server-side functions) - tuinstoel
[0] [2009-08-25 04:25:07] community_owned

What about extended stored procedures, are they numbered too?

What are extended stored procs? What is the difference between non-extended sp's and extended sp's? Please explain! - tuinstoel
[0] [2009-08-25 22:34:44] community_owned

stored procedure days are numbered... as much as any other technology that is in use today that is.

As long as there are relational databases there will be stored procedures being heavily used.

As long as there is a huge development community based around the 'database environment' (as opposed to a 'NET programming' environment) there will be stored procedures being heavily used.

One thing you could say is that stored procedure use will reduce in the .NET programming community due to Linq 2 SQL and Entity framework.

Do you think that the use of stored procs is limited to relational databases? A non-relational database like MongoDB also has stored procs (they are called stored server-side functions). - tuinstoel
[0] [2009-08-26 23:08:23] community_owned

business logic needs to be stored somewhere and if there are many applications using one database, it can make sense for that business logic to be in a stored procedure.

[0] [2009-08-27 02:23:51] community_owned

You can use stored procedures in Linq 2 SQL as well.

[0] [2009-12-02 21:35:05] tuinstoel

I hope that one day JIT compilers are smart enough to decide where a piece of code should run. Bring the data to the code or the code to the data? The JVM and the Common Language Runtime should decide and choose the best performing option for each piece of code. We devs don't have to know. A little bit like Microsoft Volta. I know that Microsoft Volta is offline but I still like the idea.

[0] [2009-12-03 05:01:22] tuinstoel

Let's take a look at the new kids on the block...

MongoDB for instance has support for stored procs (you code them in JavaScript), they are called stored server-side functions in the manual. See .

If you use the in-memory OLTP database H-store you have to call a stored proc, you can't do it otherwise. Every call to a stored proc is one trancaction. You have to code those stored procs in C++ but in the future it will probably be Ruby.

MapReduce is all about bringing the code to the data instead of bringing the data to the code so it is basically uses 'stored procs' that you can write in a lot of languages like Java or Python. The amounts of data that are processed in a MapReduce-system is massive so it is much faster to process the data local.

Aster Data focuses more and more on pushing the application logic into the MPP environment because pulling data from the database to the application layer takes too much time.

I don't think that stored procs are dead. I just think that the programming languages will change, no longer T-SQL or PL/SQL but Python, Ruby, C# or Java.

[0] [2009-06-11 19:36:10] StarShip3000

They say this just like those who said the mainframe was dead 20 years ago. Yet mainframe systems are still of real value to some businesses. Dead is relative to your mind set and needs. In 20 years from now when I'm getting paid 10x more to maintain those old stored procedures I will be very happy. LONG LIVE THE STORED PROCEDURE!

[0] [2009-08-19 06:10:55] community_owned

no, stored procedures are not dead.

[0] [2009-08-19 06:17:22] community_owned

and linq to sql isnt dead either

[0] [2009-08-20 07:06:14] community_owned

stored procedures are very userful

[0] [2009-08-20 07:09:48] community_owned

dynamic sql is useful to, but you should be careful about sql injection

[0] [2008-10-19 20:41:04] Craig

I have absolutely no problem using stored procedures for batch operations that can enjoy the performance benefits of stored procedures. But for CRUD operations there are no benefits. There is no performance benefit, security benefits are debatable, and putting business logic in the DB makes the system considerably less maintainable than putting it in well designed domain layer. A good ORM will 'generate' good SQL for these CRUD operations, not dynamic SQL scattered throughout the system.

(1) I agree that ORMs are useful for certain scenarios, but What happens when you have a large, shared database used by many applications, languages, and technologies? - Gregory Higley
Funny thing, most pro-sprocs argue with direct access to the db, while most 'sprocs are dead' take a separate business layer as single contact to the DB for granted. - Sam
Have used Hibernate ORM and it's great for insert/update side, but is poor for adhoc query and report query where dealing with result sets. iBATIS really shines for that. In the end I found it easier to just use iBATIS for everything (including CRUD). Alas, ORMs tend toward leaky abstraction problem - RogerV
The security benefits aren't debatable. If someone breaks into the database using your application account and only stored procs are available the account can't do a whole lot. If the account has access to the tables, the attacker could dump the entire table. Recent attacks at TJ Max, and Credit Card processors should prove security needs to be taken very sersouly. - mrdenny
Why is breaking into the application account more likely than breaking into the database account? If they break your database account can't they do the same thing? If your security is compromised you are stuffed either way. - Craig
because if you know what you are doing with database security, then you do not have to expose the database account in the same ways that the application account is (which is part of what sprocs do for you wrt security). Expsure is a fundamental concept of any securiy model anywhere. - RBarryYoung
[0] [2008-10-19 21:25:34] Shahin

I use SubSonic for my DAL generation, however I still use stored procedures where needed, normally when I need to do multiple joins.

If stored procedures are going to die, they're not going to die for a long time.

[-1] [2008-10-19 17:35:12] Adron

This is kind of a loaded question.

It boils down to the age old truth, whatever is getting built it better have some ROI.

Writing stored procedures generally just increases the overhead and workload of the developer. A good, skilled, and forward thinking developer is going to use an OR/M to get 90%+ of the database work done for the application. There really is no reason to use stored procedures for application development anymore, and if they are being used in large part, it's costing more than it should for the application.

I will add the clause, there are always times and places for stored procedures, generally, there aren't many times and places left these days.

...NOTE: I used to be in the stored procedures only camp. Mostly for security reasons though, and because at the time I had done most projects without any decent OR/M.

[-1] [2008-10-24 16:09:20] Will Dieterich

Top thing, this discussion of stored procedures vs dynamic SQL is only for basic CRUD statements if you have a huge statement that has to use multiple tables, parse values and then return a single value use a stored procedure.

First in MS-SQL Server stored procudures and dynamic SQL are not treated differently, they are both combined and cached. This has been the case for almost a decade, since MS-SQL 2000, IIRC since MS-SQL 7 but not sure on that. In use stored procedures are actually slower because you have all the conditional statements, isNull and COALESCE are very slow.

For SQL injection SP offer no additional protection over dynamic sql, it is only the use of parameters that prevent it.

Finally if you are doing a large program get yourself an ORM, LINQ is poor, and don't worry about it; and if you not use parameters in your SP or dynamic SQL you have something to learn over this weekend and do so quickly.

dynamic SQL is slower - first it has to be parsed by the server, then the query cache has to be checked for it, or a new execution plan has to be generated. Eventually the sql gets executed. SPs do all that when written. - gbjbaanb
gbjbannb - In MS-SQL that that stopped with MS-SQL 7.0 since 2000 they are the same. - Will Dieterich
This response is a trainwreck of factual errors. - David Lively
David, such as what?? - Will Dieterich