Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I'm working at a project in one of the world's top 3 IT consulting firms, and was told by a DBA that company best practice's state stored procedures are not a "best practice". This is so contrary to everything I've learned.

Stored procedures give you code reuse, and encapsulation (two pillars of software development), security (you can grant/revoke permissions on an individual stored proc), protect you from SQL injection attacks, and also help with speed (although that DBA said that starting with SQL Server 2008 that even regular SQL queries are compiled if they are run enough times).

We're developing a complex app using Agile software development methodology. Can anyone think of good reasons why they wouldn't want to use stored procs? My guess was that the DBAs didn't want to maintain those stored procs, but there seem to be way too many negatives to justify such a design decision.

share|improve this question

migrated from stackoverflow.com Apr 6 '11 at 22:02

This question came from our site for professional and enthusiast programmers.

14  
Bear in mind that most large I.T. consulting firms have a motive to maximise billable hours while keeping their arse covered. The old-timers with any clout in these firms also tend to be players and bureaucrats rather than techies. I'd take things like that from a consultancy firm with a grain of salt - I've gotten consultancy firms out of the shit on more than one occasion by fixing up their 'best' practice. –  ConcernedOfTunbridgeWells Feb 17 '13 at 11:35
show 3 more comments

22 Answers

I just wanted to make a concise overview of how I would recommend using stored procedures. I do not think they are a bad practice at all, and like others have said they should be used in the proper situations.

I can see problems where writing procedures for different applications can become confusing in functionality and separate the business logic of the application and result in the database becoming more disorganized and restrictive as well.

Therefore, I would use a stored procedure in relational data-oriented tasks specific to the database. In other words, if there is logic used for database operations that is consistent with the data for any application, a stored procedure could be used to keep the data stored consistently (makes sense). I think good examples of this are: consistent logging, consistent maintenance, working with sensitive information, etc.

Other tasks the manipulate the data to fit the needs of the application that follow the strong data model of the database, I think, should then be stored in another layer containing the business logic. In short, database specific data manipulation for consistency could use stored procedures, where the consistency extends past the database integrity schema model.

share|improve this answer
add comment

Programming solo, I cannot resist writing stored procedures.

I'm using MySQL primarily. I have not used object-oriented databases before like PostGreSQL, but what I'm able to do with SP's in MySQL is abstract the table structure away a little bit. SP's allow me to design these primitive actions, whose inputs and outputs will not change, even if the database underneath it does change.

So, I have a procedure called logIn. When you log in, you always just pass username and password. The result passed back is the integer userId.

When logIn is a stored procedure, now I can add additional work to be done at login that happens at the same time as the initial log in. I find a series of SQL statements with embedded logic in a stored procedure easier to write than the (calling environment FETCH) -> (get result) -> (calling environment FETCH) series you have to do when you write your logic server side.

share|improve this answer
add comment

I want to cover both some pro- and con- issues with stored procs. We use them extensively with LedgerSMB, and our rule is, with a few very specific extension, "if it's a query, make it a stored proc."

Our reason for doing this was to facilitate cross-language query reuse. There isn't a better way to do this honestly.

In the end the question is always on the details. Well used, stored procedures make things a lot easier, and poorly used they make things a lot harder.

So on to the con side.

  1. As traditionally used, stored procedures are brittle. Used alone they create the possibility of adding bugs to your code in places you didn't expect for no reason other than that the call syntax changed. Used alone this is a bit of a problem. There is way too much cohesion between layers and this causes problems.

  2. Yes, it is possible to have intra-sproc sql injection if doing any dynamic sql. It is a bad thing to be over-confident in this area, and consequently one needs to have significant experience in security in this area.

  3. Changes to interfaces are somewhat problematic with stored procedures for reason #1 above but this can become a very big nightmare if large numbers of client apps are involved.

The above are pretty hard to deny. They happen. Everyone, pro-SP and anti-SP has probably had horror stories regarding these. The problems aren't unsolvable but if you don't pay attention to them you can't solve them (in LedgerSMB we use a service locator to dynamically build SP calls at run-time, avoiding the above issues entirely. While we are PostgreSQL only, something similar could be done for other db's).

On to the positives. Assuming you can solve the above problems, you get:

  1. The possibility of enhanced clarity in set operations. This is particularly true if your query is very large or very flexible. This also leads to enhanced testability.

  2. If I have a service locator already working in this area, I find stored procedures speed up pace of development because they free up the application developer from db concerns and vice versa. This has some difficulties in doing right but it is not that hard to do.

  3. Query re-use.

Oh and a few things you should almost never do in an SP:

  1. non-transactional logic. You sent the email that the order was shipped but the transaction rolled back... or now you are waiting to continue for the email server to come online.... or worse you roll back your transaction just because you can't reach the email server....

  2. lots of little queries loosely strung together, sprinkled with procedural logic....

share|improve this answer
show 1 more comment

Considering all the above cases, I would like to add one more. The choice of SP may be depends on people choice as well.

I personally feel frustrated when people puts very complex logic in SP and I believe such SP is very complex to maintain and debug. Even many cases the developer himself faces problem when debugging in code behind (say language part) is much more easier than in SP.

SP should only be used for simple operations. Well that is my choice.

share|improve this answer
add comment

First

Stored Procedures facilitate the creation of large projects because they isolate complex data access code is some reusable piece of code (SQL). So SPs logic can be changed without having to change the application code. It results on more scalable and maintainable applications. In the middle that your SQL sentence looks to be complex or very large you should prefer SPs that embedded SQL code.

Alternatives

  • Using frameworks for mapping database relational objects, to business objects (entities), Hibernate, Entity Framework/LINQ-SQL, you can have strongly typed database access.

  • Using QueryItem clasess in the data-access layer for storing SQL
    sentencies

  • Using XML files for storing SQL sentencies

Cases when you should not use SPs

  • When the sql sentence is simple or single command

  • When the sql sentece is created dynamically based on user input. (Also inside the Sp you can use dynamic SQL)

  • When you are using an ER-mapping tool because you want to change from database to database without worrying about the sql dialect.

share|improve this answer
4  
How does this answer the poster question of why some people consider SP bad ? The question after all is : "Can anyone think of good reasons why they wouldn't want to use stored procs?" –  Gilles Apr 6 '11 at 22:18
show 1 more comment

This is another point not mentioned yet:

Code generation tools and reverse engineering tools cannot really cope well with stored procedures. The tool generally cannot tell what the proc does. Does the proc return a resultset? Several resultsets? Does it fetch its resultsets from several tables and temporary-tables? Is the proc just an encapsulated update statement and does not return anything? Does it return a resultset, a return value and some "console output"?

So if you want to use a tool to create a data-transfer-object DTO and DAO layer automatically (such as liferay's "service builder" does) , you cannot easily do so.

Moreover, ORMs like Hibernate cannot really work properly when the data source is an SP. Data access is read-only at best.

share|improve this answer
show 1 more comment

I agree with Mark that the community has really been moving away from stored procedures for quite some time now. While many of the points the original poster raised why we may want to use SPs were valid at one time, it has been quite a while and, as another poster said, the environment has changed. For instance, I remember one argument FOR using SPs 'back in the day' was the performance gains achieved because their execution plans were 'pre-compiled' while dynamic SQL from our code had to be 're-compiled' with each execution. This is no longer the case as the major databases have changed, improved, adapted, etc.

That said, we are using SPs on my current project. The reason is simply because we are building new applications on top of an existing database that still supports legacy applications. As a result, making schema changes is very difficult until we turn off the legacy apps. We made a conscious decision to design our new applications based on the behavior and rules required for the application and to use the SPs to temporarily interface with the database the way we'd like it to be and allow the SPs to adapt to the existing SQL. This goes to a previous poster's point that SPs make it easier to make changes at the database level without requiring changes to the application code. Using SPs as an implementation of the Adapter pattern certainly makes sense to me (esp. given my current project), and may be the only argument I can really see FOR their use today.

Fwiw, it is our intention to remove the SPs when the schema is updated. But, as with everything else in corporate development, we'll see if that ever happens! [grin]

share|improve this answer
add comment

It's always a source of problems to have the business logic splitted up between different layers, using different programming languages. Tracing a bug or implementing a change is way harder when you have to switch between worlds.

That said, I know companies that do pretty well by putting all business logic into PL/SQL packages living in the database. Those are not very large applications, but not trivial either; say 20K-100K LOC. (PL/SQL is better suited for that kind of system than T-SQL, so if you only know T-SQL, you probably shake your head in disbelieve now...)

share|improve this answer
add comment

The rationale is that relying on a stored procedure layer limits portability and ties you to a certain DB. Added maintenance costs are also cited as a reason. I also wanted to comment on this point you made:

(stored procedures) protect you from SQL injection attacks

It's actually the parametrized querying that protects you, which you can easily do in plain text sql querying.

share|improve this answer
10  
And if your stored proc is using any type of dynamic sql along with a string parameter, you're right back where you started. –  JeffO Apr 7 '11 at 17:52
3  
The difference is that access permission can be set for stored procedures on per procedure basis, for parameterised SQL queries you have to rely on programmers sanity not to do + "blablabla" because you have to allow plain SQL, and that's where control ends. –  Coder Apr 20 '12 at 3:58
8  
I've never understood the "ties you to a certain DB" argument. How often do you take your program and migrate it to an entirely different database? –  Mason Wheeler Jun 14 '12 at 18:07
4  
@MasonWheeler - +1 every time. In any sufficiently large project, your app ends up being written against the foibles of a given DB product. Converting to another DB becomes a major job no matter what because the new DB will have different oddities! –  Michael Kohne Jun 14 '12 at 18:09
2  
@HLGEM - but in the COTS world, multiple DBs are EXPECTED at the outset (in fact, you choose the compatible DBs). It's not that you port, it's that you support different back-ends, which is a completely different beast than doing a port. –  Michael Kohne Jun 5 '13 at 18:34
show 1 more comment

Some Observations

Stored procedures give you code reuse, and encapsulation (two pillars of software development),

Only if you use them correctly in the context in which they are supposed to be used. The same claim can be said about functions (in structured programming) or methods (in object oriented programming), and yet, we see 1K functions and mega-ass objects.

Artifacts don't give you those benefits. The proper usage of those artifacts is what give those benefits.

security (you can grant/revoke permissions on an individual stored proc),

Yes. This is a good point and one of the main reasons I like stored procedures. They provide a finer-granularity access control than what can be typically achieved with just views and user accounts.

protect you from SQL injection attacks,

That is not specific to SPs since you can achieve the same level of protection with parameterized SQL statements and input scrubbing. I would use SPs in addition to those, however, as matter of "security in depth".

and also help with speed (although that DBA said that starting with SQL Server 2008 that even regular SQL queries are compiled if they are run enough times).

This is highly database vendor specific, but in general your DBA is right. SQL statements (either static or parametrized) do get compiled. SPs help if you want/need to aggregate and compute data that you cannot do with simple SQL statements, but are tightly integrated with SQL and does not warrant the round-trip to the app server.

A good example is querying data into a temporary cursor (or cursors) from which to run another SQL itself. You can do it programmatically in the app server, or you can save the multiple round-trips by doing it in the db.

This should not be the norm, however. If you have many of those cases, then that is a sign of bad database design (or you are pulling data from not-so compatible database schemas across departments.)

We're developing a complex app using Agile software development methodology.

Agility has to do with software engineering processes and requirement managements, and not technologies.

Can anyone think of good reasons why they wouldn't want to use stored procs?

Wrong Question

The question is wrong and equivalent to asking "are there any good reasons not to use GOTO"? I side with Niklaus Wirth more than with Dijkstra on this subject. I can understand where Dijkstra's sentiment came from, but I do not believe it is 100% applicable in all cases. Same with store procs and any technology.

A tool is good when used well for its intended purpose, and when it is the best tool for the particular task. Using it otherwise is not an indication that the tool is wrong, but that the wielder doesn't know what he/she is doing.

The proper question is "what type of stored procedure usage patterns should be avoided." Or, "under what conditions should I (or should not) use stored procedures". Looking for reasons not to use a technology is simply putting the blame on the tool as opposed to placing the engineering responsibility squarely where it belongs - in the engineer.

In other words, it is a cop-out or a statement of ignorance.

My guess was that the DBAs didn't want to maintain those stored procs, but there seem to be way too many negatives to justify such a design decision.

What they are doing then is projecting the results of their bad engineering decisions on the tools they used poorly.

What to do in your case?

My experience is, when in Rome, do as the Romans do.

Don't fight it. If the people at your company want to label store procs as a bad practice, let them. Be advised however, that this can be a red flag in their engineering practices.

Typical labeling of things as bad practice is usually done in organizations with tons of incompetent programmers. By black-listing certain things, the organization tries to limit the damage inflicted internally by their own incompetence. I shit you not.

Generalizations are the mother of all screw ups. Saying that stored procs (or any type of technology) are a bad practice, that's a generalization. Generalizations are cop-outs for the incompetent. Engineers do not work with blatant generalizations. They do analysis on a case-by-case basis, do analysis trade-offs and execute engineering decisions and solutions according to the facts at hand, in the context in which they are supposed to solve a problem.

Good engineers do not label things as bad practice in such generalizing ways. They look at the problem, select the tool that are appropriate, make trade-offs. In other words, they do engineering.

My opinion on how not to use them

  • Don't put complex logic beyond data gathering (and perhaps some transformations) in them. It is ok to put some data massaging logic in them, or to aggregate the result of multiple queries with them. But that's about it. Anything beyond that would qualify as business logic which should reside somewhere else.

  • Don't use them as your sole mechanism of defense against SQL injection. You leave them there in case something bad makes it to them, but there should be a slew of defensive logic in front of them - client-side validation/scrubbing, server-side validation/scrubbing, possibly transformation into types that make sense in your domain model, and finally getting passed to parametrized statements (which could be parametrized SQL statements or parametrized stored procs.)

  • Don't make databases the only place containing your store procs. Your store procs should be treated just as you treat your C# or Java source code. That is, source control the textual definition of your store procs. People rant that store procs can't be source controlled - bullcrap, they just don't know what the bloody hell they are talking about.

My opinion in how/where to use them

  • Your application requires data that needs to be transposed or aggregated from multiple queries or views. You can offload that from the application into the db. Here you have to do a performance analysis since a) database engines are more efficient that app servers in doing these things, but b) app servers are (sometimes) easier to scale horizontally.

  • Fine grain access control. You do not want some blowtard running cartesian joins in your db, but you cannot just forbid people from executing arbitrary SQL statements just like that either. A typical solution is to allow arbitrary SQL statements in development and UAT environments, while forbidding them in systest and production environments. Any statement that must make it to systest or production goes into a store procedure, code-reviewed by both developers and dbas.

Any valid need to run a SQL statement not in a store proc goes through a different username/account and connection pool (with the usage highly monitored and discouraged.)

  • In systems like Oracle, you can get access to LDAP, or create symlinks to external databases (say calling a store proc on a business partner's db via vpn.) Easy way to do spaghetti code, but that's true for all programming paradigms, and sometimes you have specific business/environment requirements for which this is the only solution. Store procs help encapsulate that nastiness in one place alone, close to the data and without having to traverse to the app server.

Whether you run this on the db as a store proc or on your app server depends on the trade-off analysis that you, as an engineer, have to make. Both options have to be analyzed and justified with some type of analysis. Going one way or another by simply accusing the other alternative as "bad practice", that's just a lame engineering cop-out.

  • In situations where you simply cannot scale up your app server (.ie. no budget for new hardware or cloud instances) but with plenty of capacity on the db back-end (this is more typical that many people care to admit), it pays to move business logic to store procs. Not pretty and can lead to anemic domain models... but then again... trade-off analysis, the thing most software hacks suck at.

Whether that becomes a permanent solution or not, that's specific to constrains observed at that particular moment.

Hope it helps.

share|improve this answer
2  
This is a really good answer. –  yfeldblum Apr 7 '11 at 23:52
1  
Good answer, but was this intended to be ironic? "Generalizations are the mother of all screw ups." –  bedwyr Feb 18 '13 at 3:28
show 1 more comment

This is so contrary to everything I've learned.

You might need to get out more. [grin] Seriously, stored procs have been on the decline for at least 10 years. Pretty much ever since n-tier replaced client-server. The decline has only been accelerated by the adoption of OO languages like Java, C#, Python, etc.

That's not to say stored procs don't still have their proponents and advocates - but this is a long-running discussion and debate. It's not new, and will likely still be going on for quite some time; IMO, the opponents of stored procs are clearly winning.

Stored procedures give you code reuse, and encapsulation (two pillars of software development)

Very true. But, so does a decently architected OO layer.

security (you can grant/revoke permissions on an individual stored proc)

While you can do that, few do it because of the serious limitations. Security at the DB level isn't granular enough to make context-aware decisions. Because of performance and management overhead, it's unusual to have per-user connections as well - so you still need some level of authorization in your app code. You can use role based logins, but you will need to create them for new roles, maintain which role you're running as, switch connections to do "system level" work like logging, etc. And, in the end, if your app is owned - so is your connection to the DB.

protect you from SQL injection attacks

No more so than doing parametrized queries. Which you need to be doing anyway.

and also help with speed (although that DBA said that starting with SQL Server 2008 that even regular SQL queries are compiled if they are run enough times).

I think that started in MSSQL 7 or 2000. There's been a lot of debates, measurements, and misinformation on stored proc vs inline SQL performance - I lump it all under YAGNI. And, if you do need it, test.

We're developing a complex app using Agile software development methodology. Can anyone think of good reasons why they wouldn't want to use stored procs?

I can't think of many reasons you would want to. Java/C#/any 3rd GL language are all much more capable than T-SQL at encapsulation, reuse and felxibility, etc. Most of which is free given a half-decent ORM.

Also, given the advice to "distribute as needed, but not more" - I think the burden of proof these days is on SP advocates. A common reason for going stored proc heavy is that T-SQL is easier than OO, and the shop has better T-SQL devs than OO. Or, that the DBA stops at the database layer, and stored procs are the interface between dev and DBA. Or, you're shipping a semi-custom product and the stored procs can be user-customized. Absent some considerations like that, I think the default for any Agile SW project these days is going to be ORM.

share|improve this answer
show 5 more comments

It's very difficult to switch database brands and utilize the same stored procedures.

Your team either doesn't have a DBA and no one else wants to have anything to do with sql.

This is nothing more than a programmer v DBA pissing contest.

share|improve this answer
add comment

Some of the reasons that I agree stored procs aren't a best practice.

  • Business and application logic should be in the code not in the database. Putting logic in the DB is mixing up concerns.
  • You can't test stored procs as seamlessly as code in your conventional unit test projects with the rest of the application logic.
  • I don't find stored procs as being conducive to test first programming when I am writing code.
  • Stored procs aren't as easy to debug as application code when you are debugging your program in your IDE.
  • Versionning control / Source control of SP vs. normal code
share|improve this answer
5  
You can just as easy do test-first programming on stored procedures. –  user1249 Apr 7 '11 at 6:57
5  
Hmmm, well... 1) The usage of db stored procedures does not necessarily imply that business logic is being put in them. 2) stored procs are some of the easiest things to unit test. 3) store procs are not necessarily conductive of test-first practices, true, but not everything that is computable can be test-first'ed. 4) debugging shouldn't be an issue since store procs should contain nothing more than easy-to-verify SQL statements and cursors. Also, debugging should take place by first testing and debugging the SQL statements in code, and then moved into store procs... just IMO btw. –  luis.espinal Apr 7 '11 at 16:30
3  
You're obviously not a DB dev. Source control, IDEs - its damn easy to debug a SP if you're using TOAD or a similar IDE, same with versioning. –  gbjbaanb Jul 21 '11 at 11:02
4  
2) on unit testing stored procs. idk about other unit test frameworks but at least with MS Test (VisualStudio.TestTools.UnitTesting), running any of the Assert methods on the stored proc at least requires a Db connection, which by definition makes it more of an integration test than a unit test. And a stored proc may reference state about the database at a global, database level. These may not be fake-able or have interfaces. –  mathStudent Jan 7 '12 at 7:50
2  
+1 In addition, stored procedure languages (pl/sql,t-sql,plpgsql, etc) are very clunky and verbose. It's much easier for me to use a scripting language to make a database connection and handle the business logic outside of the database. –  Jack Maney Jun 13 '12 at 19:31
show 1 more comment

Stored procedures give you code reuse, and encapsulation (two pillars of software development),

Yes, but at the cost of being able to meet other agile design goals. They're more difficult to maintain, for one thing. If the project I'm on is any indication, you'll likely end up with multiple, incompatible SPs that do essentially the same job, with no benefit.

protect you from SQL injection attacks,

No. They do not. I can't even begin to guess where this idea might have come from, as I hear it said often, and it's simply not true. It may mitigate certain types of SQL injection attacks, but if you're not using parametrized queries in the first place, that won't matter. I can still ';DROP TABLE Accounts; --

and also help with speed (although that DBA said that starting with SQL Server 2008 that even regular SQL queries are compiled if they are run enough times).

They're also typically compiled when you use prepared, parametrized statements (at least with several of the DBs I've used). By the time your application begins executing the query (or especially when you execute the same prepared query multiple times), any performance advantage that you think your SP has is completely moot.

The only reason to use a stored procedure, IMHO, is when you must make a complex, multi-staged query that pulls from multiple collated sources. SPs should not contain low-level decision logic, and they should never simply encapsulate an otherwise simple query. There are no benefits and only many drawbacks.

Listen to your DBA. He knows what's up.

share|improve this answer
1  
Red Gate have a product SQL Source Control for SQL Server, but I agree, pushing logic into stored procs is an excellent way to ensure that you have important logic not under any sort of version control. –  Carson63000 Apr 7 '11 at 11:59
14  
@greyfade - "I have yet to see source control for SPs" - are you kidding me? A store proc is just a bloody text file that you upload in your database engine (which takes it, compiles it and installs it for execution.) Every place I've worked that has stored procs, we store the store proc source code in, say, CVS, clearcase or whichever SCM that was in use. Saying that store procs cannot be source-controlled (because they are in the db) is like saying my application source code (Java, C# or whatever) cannot be source controlled because it is compiled and deployed in production. –  luis.espinal Apr 7 '11 at 16:34
2  
@luis.espinal: I did not say they could not be in source control. I merely said that I did not know of a tool specifically for maintaining the history of SPs, implying maintaining that history within the database. Please don't rant at me just because you misread something. –  greyfade Apr 7 '11 at 16:39
1  
@luis.espinal is it typical that the source of a stored procedure can be retrieved later from the database? If so, you can just have a tool that pulls it out regularily, and have other tooling in place to recreate an installation from scratch. Do that once in a while to ensure it is accurate. –  user1249 May 5 '12 at 13:20
show 4 more comments

IMO it depends. Stored procedures have their place, but they are not a best practice, nor are they something to be avoided at all costs. A smart developer knows how to properly evaluate a given situation and determine if a stored procedure is the answer. Personally I am a fan of using an ORM of some kind (even a basic one like raw Linq to Sql) rather than stored procedures except maybe for predefined reports or similar but again it's really a case-by-case basis.

share|improve this answer
show 1 more comment

Besides distributing business logic needlessly, and tying you to a specific database vendor, I also firmly believe in using a technology for what it was intended. A database is just that, a relational data store. Use it to store data, nothing else.

Choose your tools wisely and you'll save yourself a world of hurt in the long run.

share|improve this answer
show 3 more comments

All three of the companies I work for used stored procedures for their application logic with SQL Server. I have not really seen things the other way. But to me they are a big mess. There typically aren't very good error handling facilities or code re-use facilities with stored procedures.

Let's say you have a stored procedure that returns a dataset you want to use, how can you use it in future stored procedures? The mechanisms on SQL Server for that are not very good. EXEC INTO...only works to one or two level)s of nesting (I forget now). Or you have to pre-define a work table and have it process keyed. Or you need to pre-create a temp table and have the procedure populate it. But what if two people call a temp table the same thing in two different procedures that they never planned to use at the same time? In any normal programming language, you can just return an array from a function or point to an object/global structure shared between them (except for functional languages where you'd return the data structure as opposed to just changing a global structure...)

How about code re-use? If you start putting common expressions into UDFs (or even worse sub queries) you will slow the code to a halt. You can't call a stored procedure to perform a calculation for a column (unless you use a cursor, pass the column values in one by one, and then update your table/dataset somehow). So basically to get the most performance, you need to cut/paste common expressions all over the place which is a maintenance nightmare... With a programming language you can create a function to generate the common SQL and then call it from everywhere when building the SQL string. Then if you need to adjust the formula you can make the change in a single place...

How about error handling? SQL Server has many errors that immediately stop the stored procedure from executing and some that even force a disconnection. Since 2005 there is try/catch but there are still a number of errors that cannot be caught. Also the same thing happens with code duplication on the error handling code and you really cannot pass exceptions around as easily or bubble them up to higher levels as easily as most programming languages.....

Also just speed. A lot of operations on datasets are not SET oriented. If you try to do row oriented stuff, either you are going to use a cursor, or you are going to use a "cursor" (when developers often query each row one by one and store the contents into @ variables just like a cursor...Even though this is often slower than a FORWARD_ONLY cursor). With SQL Server 2000 I had something that was running for 1 hour before I killed it. I rewrote that code in Perl and it finished in 20 minutes. When a scripting language that is 20-80x slower than C smokes SQL in performance, you definitely have no business writing row oriented operations in SQL.

Now SQL Server does have CLR integration and a lot of these issues go away if you use a CLR stored procedures. But many DBAs don't know how to write .NET programs or turn off the CLR due to security concerns and stick with Transact SQL.... Also even with the CLRs you still have issues of sharing data between multiple procedures in an efficient way.

Also in general the hardest thing to scale out is the database. If all your business logic is in the database, then when the database becomes too slow you are going to have issues. If you have a business layer you can just add more caching and more business servers to boost performance. Traditionally another server to install windows/linux and run .NET/Java is much cheaper than buying another database server and licensing more SQL Server. SQL Server does have more clustering support now though, originally it did not really have any. So if you do have a lot of money, you can add clustering or even do some log shipping to make multiple read only copies. But overall this will cost much more than just having a write behind cache or something.

Also look at the Transact-SQL facilities. String Manipulation? I'll take the Java String Class/Tokenizer/Scanner/Regex classes any day. Hash Tables/Linked Lists/Etc. I'll take the Java Collection frameworks, etc... And the same for .NET... Both C# and Java are way more evolved languages than Transact SQL...Heck coding with Transact-SQL makes me jealous of C...

On the plus stored procedures are more efficient for working with a big dataset and applying a multiple queries/criteria to shrink it down before returning it to the business layer. If you have to send a bunch of huge datasets to the client application and break down the data at the client it will be much more inefficient than just doing all the work at the server.

Also stored procedures are good for security. You can cut all the access to the underlying tables and only allow access through the stored procedures. With some modern techniques like XML you can have stored procedures that do batch updates. Then all access is controlled through the stored procedures so as long as they are secure/correct the data can have more integrity.

The SQL injection argument doesn't really apply so much anymore since we have parameterized queries on the programming language side. Also really even before parameterized queries a little replace("'", "''") worked most of the time as well (although there are still tricks to use to go past the end of the string to get what you want).

Overall I think SQL and Transact SQL are great languages for querying/updating data. But for coding any type of logic, doing string manipulation (or heck file manipulation....you'd be surprised what you can do with xp_cmdshell....) please no. I'm hoping to find a future place that does not use stored procedures mostly. From a code maintainability point of view they are a nightmare. Also what happens if you want to switch platforms (although really if you paid for Oracle/DB2/Sybase/Sql Server/etc. you might as well get everything you can out of them by using every proprietary extension you can that helps you out...).

Also surprisingly often the business logic is not the same. In the ideal world you would put all the logic in stored procedures and share that between applications. But quite often the logic differs based on the applications and your stored procedures end up becoming overly complex monoliths that people are afraid to change and do not understand all the implications of. Whereas with a good object oriented language you can code a data access layer which has some standard interface/hooks that each application can override to their own needs.

share|improve this answer
show 1 more comment

Who do you work for?

The answer may depend on who you are employed by, the consulting firm or the company itself. What's best for a company is very often not best for a consulting firm or other software vendor. e.g. A smart company desires to have a permanent advantage over its competitors. By contrast a software vendor wants to be able to hawk the same solution to all the businesses in a particular industry, for the lowest cost. If they are successful in this, there will be no net competitive advantage for the client.

In this particular case, applications come and go but very often, the corporate database lives on forever. One of the primary things an RDBMS does is to keep junk data from entering the database. This can involve stored procedures. If the logic is good logic and highly unlikely to change from year to year, why should it not be in the database, keeping it internally consistent, irrespective of whatever application is written to use the database? Years later someone will have a question they want to ask of the database and it will be answerable if junk has been prevented from entering the DB.

So maybe this has something to do with the fact that your DBA works for a consulting firm. The more portable they can make their code, the more they can reuse code from client to client. The more logic they can tie up in their app, the more the company is wedded to the vendor. If they leave behind a big mess in the process, they will get paid to clean it up, or never see the mess again. Either way it's a win for them.

enter image description here

For (lots) more discussion on both sides of the fence, read the discussion at coding horror. FWIW I lean to the side of those advocating for SPs.

share|improve this answer
2  
@HLGEM I don't object to any of the points you brought up. But I object to the answer's thesis that the primary reason the DBA might put logic into the application is because he is a consultant and intends to screw the customer. It ties a person's moral standing to his choice of whether to use stored procedures. In my opinion, there are technical arguments on both sides, and the arguments on both sides will differ from application to application, technology to technology, company to company, industry to industry. And I will first look for merit before impugning motive. –  yfeldblum Apr 7 '11 at 23:30
show 4 more comments

I want to also point out that stored procedures do use cpu time on the server. Not a lot, but some. Some of the work done in the work procedure could be done in the app. It's easier to scale the app layer than the data layer.

share|improve this answer
2  
It's hard to scale a database? –  JeffO Apr 7 '11 at 17:47
1  
It's at least significantly more expensive (unless your on MySQL) and in many places I've worked getting another SQL Server Enterprise Edition license is like pulling teeth –  ben f. Apr 7 '11 at 18:16
add comment

How do you version stored procedures on the server?

If you redeploy stored procedures to the server from version control, you blow out the stored execution plan.

Stored procedures should not be modifiable directly on the server, otherwise how do you know what's really running _right now? If they are not, the deployment tool needs access to write stored procedures to the database. You'd have to deploy on every build (exec plan might need to be different)

While stored procedures are non-portable, neither is SQL in general (ever seen oracle date handling--uggghhh).

So, if you want portability, build an internal data access API. You can call this like function calls, and internally you can build in in whatever lingo you want, with parameterized queries, and it can be version-controlled.

share|improve this answer
4  
How do you version stored procedures on the server? - you version control the store proc source code. When it's time to deploy, you grab the store procs (from a given baseline) and you (or your dba) deploy to production. Redeployment (be it on testing or production) certainly blows out the stored exec plan, but that will happen independently of whether you source control your SPs or not. –  luis.espinal Apr 7 '11 at 16:38
1  
@BarryBrown It doesn't work if people have access to the server directly and can change the stored procedures. I'd have to have a process that watches the SPs, or have a check before each use... –  Christopher Mahan May 7 '12 at 21:40
1  
One of the things I've done in the past has been to put a development instance of the database server on individual developer's workstations, or if that wasn't possible, then at least have "dev" and "production" instances of the databases, and all the DDL and DML scripts, as well as sample data and load scripts lived under their own directory in the source tree, and the database was routinely built from those scripts using the MAKE file. Devs were able to use nmake to build single stored procs, as well. If they didn't put it under source control, it would disappear on them, and they knew it. –  Craig Mar 21 at 23:10
1  
...I didn't mean to sound disparaging in my earlier comment, with the "..., even if you're not aware..." phrase. What I meant to convey was that if that sort of thing is happening with stored procedures, it's probably happening in other parts of the project, as well. I personally kind of dislike integrated source control in IDE's, in part because I think it makes people kind of lazy in terms of thinking about what it really means to the team and project as a whole to make changes and commit those changes to the source control repository. Those things should not be "automatic" in my opinion. –  Craig Mar 21 at 23:16
show 6 more comments

In my experience working on very large projects, you have to be very clear on where business logic lives. If you allow an environment where individual developers can put business logic in the business object layer or in a stored procedure as they see fit, a large application becomes VERY difficult to understand and maintain.

Stored procedures are great for speeding up certain DB operations. My architectural decision is to leave all logic in the business layer of the application and employ stored procedures in a targeted manner to improve performance where benchmarking indicates it is warranted.

share|improve this answer
21  
I don't see things quite that simply. To me, it's ALL business logic. The database, with or without stored procedures, provides certain services and makes certain guarantees. Ideally it should be impossible for incorrect application code to put the database into an inconsistent state. If stored procedures are needed to maintain that consistency, I use them. –  kevin cline Apr 7 '11 at 2:38
4  
@kevin cline: Define "inconsistent state". I agree that DB features such as referential integrity are valuable and greatly reduce the prospect of an application error causing serious damage. However, generally speaking, the definition of "consistent data" depends on correct execution of business rules. –  Eric J. Apr 7 '11 at 3:46
10  
add my million to Mayo's million. Distributed business logic takes you off of the highway of good practice, straight into the lane of lunacy –  Nico Apr 7 '11 at 9:23
16  
+1 Business logic seeping into the DAL is a great concern when using stored procedures. –  System Down Apr 7 '11 at 15:59
12  
@ChristopherMahan, I would NEVER want to use a database you design. That is the worst possible practice from a database perspective. Databases are often affected directly at the database. It is short-sighted to think someone will use the business layer to update a million records or other things that happen over time. Imports do not typeically go through the business layer (yep I want to process my 21 million record import one record at a time in the business layer). Fraud is much easier when you don't have constraints at the database level. Bad data is almost 100% certain. –  HLGEM Jan 6 '12 at 21:05
show 8 more comments

This was the official line when I worked for one of the Big Five a few years back. The rationale was that since SPs are tied to particular implementations (PL/SQL vs T/SQL vs ...), they unnecessarily limit technology choices.

Having lived through the migration of one large system from T/SQL to PL/SQL, I can understand the argument. I think it's bit of a canard though - how many places really move from one database to another on a whim?

share|improve this answer
10  
@DaveE: For an enterprise solution you're probably right. If you're creating packaged software, as soon as you ship on MSSQL your biggest prospect will want it to run on Oracle. –  Eric J. Apr 6 '11 at 22:09
3  
@Eric: too true. Where I'm at now, we use tons of SPs and tell folks 'no' if they don't want MSSQL. It's nice to be able to do that. –  DaveE Apr 6 '11 at 22:12
2  
@DaveE: Does the sales team wish you could say "yes"? –  Eric J. Apr 7 '11 at 3:48
1  
It is not as much moving one system from one database to another, but having one system being able to use whatever database system the customer already has. Big databases are expensive. –  user1249 Apr 7 '11 at 7:03
show 5 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.