Yes, yes yes! You hear these arguments all the time, and I've never really bought into them. Using stored procedures in an app basically means that you've a) added another layer, b) added another language and c) lost all database portablility. At the very least. I don't think I've ever heard anyone go into the details around the ambiguity of SQL error messages and debugging problems before, though (kudos).

In my mind, they're just not worth the added effort in maintenance, unless you encounter one of those situations where you can *actually* get a real, calculable performance gain from them. Otherwise, I'm all for KISS. Use a good OR Mapper instead.

So will Yukon help with any of these problems?

hear hear! The last big project I did I mixed the usage of Stored Procedures and dynamic SQL, and the result is absolutely great.

You get the performance or adaptability where you need it (it was for an ISV) and can keep the maintenance to a minimum (we even supported both SQL Server and Oracle).

Besides the performance aspect, adaptability was our main reason for using SPs at all, because you can tweak the client's configuration just a bit more using out-of-application SQL statements ;-)

Performance issues aside, the security implications are worth using stored procs. I don't buy the argument of convenience of development or portability - it's just laziness. It is a pain to debug apps when the environments are not integrated. I suggest you use parameterized dynamic SQL during development to get your SQL right and then create the stored proc based on that.

In some cases, when you are not the one who develops both the stored proc. and the calling code, you don't necessarily want or need to know what the stored proc. is doing. If something goes wrong with the app, it should be pretty obvious if the problem is in the stored proc. or in your code. If not, then it's either a problem in the design or the code that you can't figure it out (of course, you are not always going to be looking at your own code or code that's easy to maintain and understand).

Stored procs should be used to perform most database operations because it allows better structure. It enforces it. It's basically the same idea of creating procedures/methods in procedural languages.

The main problems, as noted, are when you want complex data structures (objects) for parameters or variable number of parameters (such as a search function with many criteria).

In larger projects where there are many developers, using stored procs will make sure that it will remain easy for everyone in the team to be able to use and reuse the same stored procs. Also in larger projects, tracking and finding SQL statements in your code can be ugly and error prone.

Basically, I agree that there are some limitations of stored procedures that I wish didn't exist, but overall, I believe stored procs. are much better for the purpose of designing and developing applications and if the performance is even marginally better than dynamic SQL, then it's well worth it.

> Stored Procedures are written in big iron database "languages" like PL/SQL (Oracle) or T-SQL (Microsoft).

> Stored Procedures typically cannot be debugged in the same IDE you write your UI.

Naked SQL statements clumsily embedded in other languages use the same 'big iron' database languages. But wrapped up as text strings like many programmers do, they're not debuggable at all, in any IDE.

> Stored Procedures don't provide much feedback when things go wrong.

Plenty of support for input validation and raising of errors exists. If you choose not to take advantage of it and then complain that it's not being done...

> Stored Procedures can't pass objects

Create a class that formats an object's properties as a stored procedure call, then use the class and forget for the rest of yout project's development cycle that either SQL or stored procedures even exist.

> Stored Procedures hide business logic.

Yes! N-tier design calls this a feature, not an issue. Your code isn't supposed to know about or depend on the details of the stored procedure implementation. It's just supposed to use the interface provided.

Stored procedures and triggers also allow for input validation, preventing a typographical or design error in other code from dumping erroneous data into the tables.

Stored procedures allow multiple sections of code to call the exact same steps for database interaction, and allows the developer to easily edit those steps and effect all dependent code at once.

Disallowing direct table access and allowing only stored procedures limits what can be done to the data to that fucntionality provided by and validated by sstored procedures. This disallows inappropriate or erroneous usage by authorized users, and severely limits what an unauthorized user can do, since they can't even list the stored procedures that exist or the code behind them.

Even arguments that 'it's too much typing' fail to hold water once you start taking into account having to surround the naked SQL calls with string quotes and line-continuation characters or string concatenation methods, factor in editing duplicated code in each place it occurs and the writing of additional client-side code to handle the input verification that could in most cases be more easily handled by the database server itself.

Hi, Jeff: You assert that "none of these things are true in practice," but present no evidence to refute these points:

* Stored procedures can be individually secured within the database. A client can be granted permissions to execute a stored procedure without having any permissions on the underlying tables.

* Stored procedures result in easier maintenance because it is generally easier to modify a stored procedure than it is to change a hard-coded SQL statement within a deployed component.

These seem to me like valid reasons to use stored procedures.

Also, VS.NET does allow SQL Server stored procedures to be "debugged in the same IDE you write your UI."

I read Jeff's post last night and was going to comment this morning, but Wen and Eric covered pretty much all the points I wanted to. I am of the mindset that coming up with justifications for removing layers of abstraction in development is sheer laziness on the part of the architect; stored procedures are there for a reason (several actually)...not only for performance gains, but to separate business logic from (in what is often the case with web development) the presentation layer.

I'm actually astounded that the first point (re: "so-called languages are archaic") was even mentioned: I don't know about you guys but all the stored procedures I've written are in T-SQL on MS SQL, which is pretty much line for line the same code I would "inline" into the web application code, if I so chose to go that route.

We are in the midst of a 3 month project with another company who is responsible for maintaining a huge Lawson HR system, and transmitting job requistion and hiree data to and from our custom hiring application. Stored Procedures are practically a must in this scenario: Their lawson developer sends us the data and we deal with it as we need, sending it back and forth via stored procedures. I need to have less than a rudimentary understanding of what is going on in Lawson, and quite frankly, it's not my responsibility with this project. I am responsible for handling the data on our end; that's *exactly* why they have a Lawson developer...he has his role and I have mine.

Business logic hidden, data transported, end of story.

> Stored procedures result in easier maintenance because it is generally easier to modify a stored procedure than it is to change a hard-coded SQL statement within a deployed component.

Maintaining our procs is a nightmare. We have a handful of guys who know PL/SQL and the Toad IDE well enough to actually edit the procs, much less debug a complex one. Things might be easier on the SQL Server side due to the MS integration, but until they get CLR in the engine (which I think is fantastic), it's still Yet Another Layer we have to deal with and keep in sync, etc. This isn't easier to maintain: it's harder!

> Stored procedures can be individually secured within the database. A client can be granted permissions to execute a stored procedure without having any permissions on the underlying tables.

Can't you just set up database logins for those users that grant access appropriately?

Of course, as in all things: it depends. You may have a project where the "all stored procs" approach makes sense. What I really object to is this blanket (and INCORRECT) perception that procs are "just better" when my practical experience indicates they are WORSE on every project I've worked on. Even for performance! You need a really *compelling* reason to use procs-- critical performance junctions, remote vendor security lockdown, etc.

> Naked SQL statements clumsily embedded in other languages use the same 'big iron' database languages. But wrapped up as text strings like many programmers do, they're not debuggable at all, in any IDE.

Regular expressions are another language "clumsily embedded" in my code, yet it makes my life much easier. Go figure.

> Yes! N-tier design calls this a feature, not an issue. Your code isn't supposed to know about or depend on the details of the stored procedure implementation. It's just supposed to use the interface provided.

The database layer is not the only place to do this. I would argue it's one of the WORST places to do it because of all the reasons I discussed in my post. There are many many ways to isolate your data access layer. My current feeling is, if you REALLY care about building a reusable, abstracted API, then you need to be building a web service. End of story. That's the only abstraction layer that, IMO, currently means anything. Everything else gives you the *illusion* of abstraction (remoting, stored procs) with few of the benefits. But once the web service is built, all my abstraction is taken care of-- I can call it from Mac, Dos, Unix, whatever, any platform, any language. It's work, but you get REAL abstraction, a huge benefit. And the back end can do procs, parameterized sql, black magic, whatever. Who cares at that point, because it's fully abstracted away.

> Stored procs should be used to perform most database operations because it allows better structure. It enforces it. It's basically the same idea of creating procedures/methods in procedural languages.

But again, are procs the only way to achieve this isolation? I don't think so, and they also have some serious downsides.

Having worked with both Oracle and SQL recently, and having seen dynamic SQL being used in both, I know that performance is definitely an issue, but SQL server developers probably understand the performance implications less than their Oracle developer counterparts. In Oracle, not using bind variables is one of the big no-no's because of SQL parsing and SQL statement caching, etc.

Dynamic SQL is definitely necessary in some cases, but where you can, you should definitely use parameterized SQL or stored procs. Stored procs are generally precompiled so you know that, as it is created in the database, that the statements are valid, so you don't have SQL syntax errors. Knowing these things should save you headaches later on. Jeff, you comment on the lack of ability to debug stored procs in the same environment as the rest of the development that you do, but I would argue that if you used stored procs, you are less likely to have to debug the SQL. Not only that, if you had stored procs, it's unit-testable.

I personally don't like to have a lot of business logic on the database side either, but I think the logic relating to data integrity, etc. and having to do with data storage must definitely be on the database side. For example, and I think most people will agree with me, if you have a data table and then you have a hist table to keep track of changes to rows in the data table, that "business logic" should stay on the database. For me, I personally like to keep the database access to storage and retrieval and move most of the business logic out of the database into a middle tier. But this really depends on the project requirements.

The project I am currently working on is extremely heavy with database-side business logic, but also has a lot of logic in the web code (ASP.NET). This is because the database is actually owned by our business unit. We have separate database developers who work on the database and the data is imported nightly from different locations. We use stored procedures almost exclusively, on SQL server, and we create dynamic SQL in the stored proc. and - get this, we pass XML in order to get around the limitation on complex data structures. So not only does the stored proc have to parse XML, it also has to create dynamic SQL string and execute it. In our case, I think it makes sense given the functional requirements.

SQL Server 2005 to the rescue then, eh?

CLR? Check
Stored Procedures have the ability to be created in a .NET language like C#, VB.NET, etc? Check

Sold? Check

Lets use a hypothetical real-world scenario to understand why stored procedures are not always the best approach:

You have 3 database platforms on 3 servers. One server in Washington runs Windows 2003 with SQL Server 2005. One server runs Oracle on Linux or a Sun (sorry don't use Oracle to know if they need some outrageous beast OS/Hardware). One server runs MySQL on a Linux box.

All 3 databases share a table and must synchronize that table. You develop an application that checks each table on each database platform to make sure the synchronization is working right. Would you rather use .NET and let the Data Providers handle all of the SQL/Oracle/MySQL work? Or would you rather have to know and access 3 different stored procedures, knowing each callback and database platform UP FRONT.

I'd use .NET with 3 data providers and using common code that generates the SQL needed to use all 3. This way you don't have to code around any inconsistencies in the underlying databases because they will all return the same .NET structures you can easily use. Can you honestly say you can use the same stored procedure and make it work exactly alike on every database platform you put it on? Simple stored procedures maybe, but they're so different that you're bound to have some kind of differences that you'll have to code around.

Then again I could just be pulling all of this out of my ass. I've never really bought into stored procedures because I want apps that work when you plugin a new data provider. I don't want to have to think "Oh you have to add this stored procedure, throw salt behind your back, cluck like a chicken 3 times, and pray that it happens to work exactly as it should".

I know it's not quite like that but I like having all of the SQL in one place. I just edit my code where the code is, not having to remember to edit both the code and the stored procedure on whatever database I happened to be working on at the time. Oracle stored procedures can't be accessed in VS.net by default, and I'm way to cheap to spend some outrageous amount of money so that I could edit all stored procedures in the IDE I'm doing my application in. SQL Server 2005 may change my thinking about all of this, but for now I'm just not seeing how stored procedures will benefit me personally. If I were dealing with customer databases and their own DBA BOFH then I may be forced to use stored procedures but I could slap the DBA whenever the procedure wasn't working right. It does add an extra layer of slappage not currently present in my workflow.

I am all for abstractions and separations of logic. I definately do not believe that T/SQL or PL/SQL are expressive enough languages to describe that logic. Sticking critically meaningful code in the DB from what I've always encountered leads to the Clusterous Fucokous Anti-pattern.

This is true regarless of where you put it. Procs or embedded.

Hurray for ORM.

> but where you can, you should definitely use parameterized SQL or stored procs

Parameterized SQL should ALWAYS be favored over dynamic SQL. Parameterization prevents SQL injection and generates more generic (and therefore more cachable) execution plans.

> Jeff, you comment on the lack of ability to debug stored procs in the same environment as the rest of the development that you do, but I would argue that if you used stored procs, you are less likely to have to debug the SQL

Not in my experience. We still need to look at the tables and understand the data to do our work; having SQL helps us do that. Procs are totally opaque.

> but I think the logic relating to data integrity, etc. and having to do with data storage must definitely be on the database side.

Definitely. The database should be relationally coherent and have a clean, understandable design. Fundamentally bad db design shouldn't be able to hide behind a bunch of stored procs. The data should be good at.. being coherent data. Not being a "customer".

> We use stored procedures almost exclusively, on SQL server, and we create dynamic SQL in the stored proc. and - get this, we pass XML in order to get around the limitation on complex data structures. So not only does the stored proc have to parse XML, it also has to create dynamic SQL string and execute it. In our case, I think it makes sense given the functional requirements

Urgh, I dunno, that sounds like a lot of extra conversion work. If you're going to do that much work, why not do a little more and write a web service API. Then you could pass true objects over HTTP, and not even care how it is handled on the database side. If you are writing stored procs that themselves build dynamic SQL, might as well cut out the middleman and move this to a web service API that generates dynamic sql. It seems your goal is abstraction, not performance, and stored procs are a terribly leaky place to build your abstraction layer, IMO.

I couldn't agree more!

JavaKid

Jeff,

I would agree with you in the case where a database will only be used by a single application being developed by a single programmer for a single customer because all of the available knowledge is in one brain and there is only one skillset to work with. However, as soon as you start adding programs, adding programmers, or adding customers I switch to being firmly in the camp that says "all interaction with the database will be through stored procedures" and here is why:

1. As a rule VB programmers are terribly inefficient T-SQL coders. They are row-based thinkers, not set-based thinkers, and that leads to very slow code. I spend a lot of my time "fixing" code written by VB programmers that "works" but takes forever. I just finished rewriting an app that used to run overnight or over a weekend (depending on the options selected) that now runs in 15 - 20 minutes. The efficiency did not come because of parameterized queries or cached queries, it resulted from using set logic on the server instead of executing hundreds of thousands of queries from the client and processing the logic on the client row by row.

2. Anyone that has had to research a proposed schema change will tell you what a nightmare it is to identify all of the code that could be affected by the proposed change. How do you know if you have found all of the code? Do you just wait and see what breaks after the change is made? Requiring that all interaction with the database be done through stored procedures makes it easy to identify the stored procedures that will be affected.

3. Stored procedures can be designed, written, and tested separately from the VB code. The VB programmer and the database programmer reach an agreement on what the parameters will be and what the output will be and they both write their code in accordance with that "contract". This allows for parallel development and independent testing.

In closing I would like to throw in a couple of other general observations:

1. You probably would not want a contractor to build your house for you if they only used carpenters. It is true that the majority of the work should be done by carpenters, but you would probably want some electricians to do the wiring and some plumbers to handle the pipes and toilets. Does that mean that carpenters couldn't build a house without electricians or plumbers? No. Would you want to buy that house? Probably not.

2. I have found that most problems with performance have their root cause in poor database design. Spending more time upfront thinking through the design and understanding how it will be used will give you more performance gain than parameterized queries vs. dynamic queries.

> it resulted from using set logic on the server instead of executing hundreds of thousands of queries from the client and processing the logic on the client row by row

That just sounds like bad coding, pure and simple. *Hundreds of thousands* of queries? How could that ever be a good idea?

> Anyone that has had to research a proposed schema change will tell you what a nightmare it is to identify all of the code that could be affected by the proposed change.

An illusion, as we have had table changes which broke dozens of stored procedures. It's the same problem, just on a different (and harder to debug) tier. Reality is, changing the DB will break everyone, which is why I favor total transparency whenever possible.

> Stored procedures can be designed, written, and tested separately from the VB code.

So can API layers. Stored procs are not the only form of database abstraction, just one of the more limited ones.

> Does that mean that carpenters couldn't build a house without electricians or plumbers? No. Would you want to buy that house? Probably not.

I agree you need developers that understand basic relational database design. I have worked with developers (I am looking at you, Shawn Holmes) who didn't even index their tables.

> I have found that most problems with performance have their root cause in poor database design. Spending more time upfront thinking through the design and understanding how it will be used will give you more performance gain than parameterized queries vs. dynamic queries

I agree. One of the advantages of parameterized SQL is that it provides much greater transparency into the database. It's a lot easier to hide a bad database design by sticking it behind a bunch of stored proc "interfaces". The DB schema should ALWAYS be well designed, and the more eyes you have on it, the more likely it is to be well designed. If 4-5 developers can't figure out the crazy queries necessary to do basic business work in your schema, maybe it isn't very good?

Jeff,

"That just sounds like bad coding, pure and simple. *Hundreds of thousands* of queries? How could that ever be a good idea?"

From the VB programmer's point of view it seemed like a good idea. The purpose of the application was to determine the eligibility of thousands of people for health care benefits based on a complex set of rules based on number of hours worked, hire date, where they worked, when they worked, and other personal information. The programmer executed a series of queries to get the necessary information for one worker for one time period. They then used that information to calculate whether or not that person had earned eligibility and executed an update query to save the eligibility information. They put that block of logic inside 2 loops - one to iterate through all of the workers and one to iterate through all of the time periods for each worker. To the VB programmer that made perfect sense - it was just like using arrays. To a database programmer that is insane, but to a VB programmer it made perfect sense.

"An illusion, as we have had table changes which broke dozens of stored procedures. It's the same problem, just on a different (and harder to debug) tier. Reality is, changing the DB will break everyone, which is why I favor total transparency whenever possible."

You seem to have missed my point. If all of the code referencing a table is contained in stored procedures I can find all of the affected stored procedures by doing one query against the syscomments table (where all stored procedure source code is stored by SQL Server). I do not know of any way that I can find all of the VB code that may exist that references a table because there is not a single known place where all of the source code resides. The issue is not how many pieces of code will be affected, the issue is how do I find all of the affected pieces of code?

"Stored procedures can be designed, written, and tested separately from the VB code.

So can API layers. Stored procs are not the only form of database abstraction, just one of the more limited ones."

Once again I think you missed my point. Whether you execute queries directly from VB or you put them into stored procedures the queries themselves must be tested. I believe that it is better (and easier) to write and test stored procedures separately so that when you are testing and debugging your VB code you have eliminated the dynamic SQL as a possible source of problems.

"It's a lot easier to hide a bad database design by sticking it behind a bunch of stored proc "interfaces". The DB schema should ALWAYS be well designed, and the more eyes you have on it, the more likely it is to be well designed. If 4-5 developers can't figure out the crazy queries necessary to do basic business work in your schema, maybe it isn't very good?"

In discussing the pros and cons of using stored procedures vs. dynamic queries from VB we should both be assuming the presence of skilled programmers on both the VB side and the database side. It is somewhat disingenuous to argue that you need to put the queries in the VB code because, if you don't, the database people will write a bunch of crappy code and stick it in stored procedures that you can't understand and are stuck with. It would be equally wrong for me to argue that VB programmers should be forced to use stored procedures because if they are allowed to write queries against the database they will be very inefficient and bog down the database server or write update queries that screw-up the data. Even though that may be true given the talent levels on your projects, the philosophical argument should assume that both sides have talented people.

The question is, if given equally talented people on both sides, what is the best way to work with a database and why?

Paul:

"The issue is not how many pieces of code will be affected, the issue is how do I find all of the affected pieces of code?"

Try Edit->Find And Replace->Find In Files

Jeff:

This has been a good read for me. I enjoy hearing both sides of the argument and really haven't had a strong opinion either way. Until now.

I believe the security and abstraction that comes with using stored procedures make them well worth implementing. I know the maintenance aspects have really saved my team on a number of occasions.

Security wise, preventing direct access to the tables make sense. It keeps them from being abused by the general public (or casual developer). Stored procedures create a standard interface that can be enforced, keeping an application developer from unknowingly putting your data into an invalid state.

Although it's bad practice, stored procedures can be modified to add functionality to a deployed application without re-release. This makes my boss happy because he doesn't like the time or the process involved in certifying and releasing builds. A stored procedure change can often take less than a few minutes and get an application back up and running. It can also add new functionality to a well designed application.

As far as being able to swap out databases, I think you can still do this with a small amount of effort. If your database supports stored procedures, you'll have to re-code them in the new database. One plus to note here is that your code should require NO changes to support this. I should say "in theory" but this has been the case for me on almost every occasion so far. If the new database doesn't support stored procedures (mySql) then you can override your standard datalayer (that interfaces to the stored procedures) and put the logic in there. A word of experience here: it takes a lot more effort to implement logic from a stored procedure in your data layer. One plus of database script languages is that they're designed specifically to manipulate data. They're very compact and focus primarily on that function.

Think about accessing the database through stored procedures (only) as a means to using your database like a service. It provides whatever (and only) the functionality implemented by your stored procedures, regardless of what application is accessing it. It you have one application and 4 import (applications) all using the database, stored procedures will help ensure that they all do it in a consistent way. New applications will already have a good baseline of database logic to choose from. You database becomes more like an entity, or single object that has specific functionality available.

If you've never tried stored procedures, you owe it to yourself to do so and formulate an opinion based on your experience. Do some more reading on the subject too. Quite often, you'll find that a development method/practice doesn't make sense because you haven't used it in the same way that other people have successfully used it.

> Think about accessing the database through stored procedures (only) as a means to using your database like a service. It provides whatever (and only) the functionality implemented by your stored procedures, regardless of what application is accessing it. It you have one application and 4 import (applications) all using the database, stored procedures will help ensure that they all do it in a consistent way. New applications will already have a good baseline of database logic to choose from. You database becomes more like an entity, or single object that has specific functionality available.

Change the word "database" to "web service" and I agree. That's a meaningful layer of abstraction for apps that need a formal API layer (note that not all do).

> If you've never tried stored procedures, you owe it to yourself to do so and formulate an opinion based on your experience

Oh, believe me, my opinion is based on extensive use of stored procs in many projects. They are the source of much continuing pain.

Check out the latest proc "submitted by Jeff Atwood" at
http://www.thedailywtf.com/ShowPost.aspx?PostID=29337

You guys are clueless if you think that stored procedures have no real world benefit. When you have worked on a database that has over 2 TB of data, and literally more than 3k tables/views you will understand not only the power, but NECESSITY of them. You use stored procedures to present an API for a schema, and get the benefit of faster execution, security, etc.

Dynamic SQL queries are an absolute killer in terms of database performance. They do not scale, at all. When your users are screaming because your query is taking 20+ seconds to generate output, and the plan analyzer shows that it's taking 19+ seconds trying to figure out what to do with it, you know it's time for an SP. Actually, up-front would have been the time. They are also a killer when it comes to maintaining consistency for client applcations, which may not always be your simple compiled application. When you have 70+ projects sitting on one giant shared database + schema you simply cannot have developers mucking about directly with tables or views unless ABSOLUTELY necessary. Otherwise when you change the schema under them, they are screwed. And so are all your users using that dirty code.

It's nice to bitch about how much of a pain in the ass they are (and I agree - they are), but if you're that vehement about how useless they are it would seem you don't really know pain yet.

> When you have worked on a database that has over 2 TB of data, and literally more than 3k tables/views you will understand not only the power, but NECESSITY of them

Sure, YMMV. What I object to most is the argument that stored procs must ALWAYS be used, no matter what, which is a pretty giant net negative.

> and the plan analyzer shows that it's taking 19+ seconds trying to figure out what to do with it, you know it's time for an SP. Actually, up-front would have been the time

What database are you using? As mentioned in the post above, parameterized SQL query plans are *automatically* cached by most modern database systems. Fex, in SQL Server as of version 7.0 or greater. So the query plan will only be calculated the first time the query is sent; all subsequent submissions of that same parameterized SQL will be looked up in the query plan cache.

> When you have 70+ projects sitting on one giant shared database + schema you simply cannot have developers mucking about directly with tables or views unless ABSOLUTELY necessary

Again, are stored procs the only way to accomplish this goal? If you want an API that really *means* something, I'd put a Web Service on top of that database and disable access from any port other than port 80.

Eric wrote:

> Naked SQL statements clumsily embedded in other languages use the same 'big iron' database languages. But wrapped up as text strings like many programmers do, they're not debuggable at all, in any IDE.

Paul Lach wrote:

> 2. Anyone that has had to research a proposed schema change will tell you what a nightmare it is to identify all of the code that could be affected by the proposed change. How do you know if you have found all of the code? Do you just wait and see what breaks after the change is made?

The answer to both of these problems is to have unit tests. It's that simple. Once you've got your 'dynamic' SQL passing its tests, you know it's right. Changed your schema and want to know if you found all the affected code? Run your tests.

You know your tests cover the affected code because you either write code test-first (no code is written until a failing test is), and/or you rely on a coverage tool, which tells you which bits of the system have been exercised.

With ADO.NET's generated typed 'DataSet' classes, you even get to see compiler errors showing you where you broke your code when your schema changes. You can just browse your 'task list' and fix each problem in turn. To make this work, all you have to do is regenerate your DataSet classes.

Obviously this is no substitute for a full set of unit tests, but it's useful nonetheless.

Rik

I believe that any DataLayer must be a simple code block, that they allow operations against DB.

That code block would not have to know on the Business Entities. Single to specialize it is to execute the operations (Store Procedures and SQL Sentences) against the engine DB (SQL, Oracle, DB2, etc.), with which this setting.

Finally, I invite to you to download the DataLayer.Primitives Public Version.

This is very cool Data Layer :)

DataLayer.Primitives - Readme!
http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=1389

Anyone who says that T/SQL is archaic while still coding in ASP deserves a boot to the head. If you use Cold Fusion, Java, or C#, that's another story.

SP is best for distinct logic building blocks with encapsulated business rules that can be treated by the developer as a black-box object (think OOP advantages).

In-line SQL is best for single queries that may additionally be cached at the app server level if 'static-enough'.

Almost every ASP.NET project I have seen (where I wasn't the architect ;-) ) the use of stored procedures has led to complete and total chaos. Part of the reason is that they tend to be there because the designers centered everything on the relational database design, making that the first priority, and part of the reason because no one can properly debug reams of bloody t-sql when you dont even know if the bug is in the t-sql or in the c#. In fact, when it comes to basic CRUD (but not complex report type or long running queries in many cases) I dont see any reason at all to use stored procedures and in my very good experience, it is dangerous to allow their use. Where this dogma about avoiding in-line sql has come from I don't know, but it should be recognised as just that - dogma. Security? Performance? Pfa! My arse!

All this has made me remember one of my biggest wish items for stored procedures: interfaces.

I recently duplicated a large set of procs to provide my code with identical field names in the results they returned, e.g. "name" and "id", vs. "<entityname>_name" and "<entityname>_id".

First let me start out by saying I have been developing database application since DOS which I had to migrate many applications to a new platform. I have worked on both stored procedure base systems and embedded SQL applications. Performance was always better with stored procedures.

I personally do not feel the business logic should reside in the application, application have their rules and data has its rules. In today’s IT world we now have many interfaces for the same application. They are thin, rich, and/or smart clients. These clients can all use the same datasets for accessing systems.

Why would you put embedded SQL in every client? That does not sound that productive. I would have to redistribute the client. With a thin client the user would get the changes the next time they access but I still have to move my code from development environment to production. With smart clients I would have to distribute the application too. Can I have smart and thin clients accessing the same datasets? Yes, so why embedded SQL?

One other argument I have seen on the Internet about stored procedures was that it was difficult to diagnose when a problem exists. What I say to these people is you do not understand databases and need to go back to school. If you think it is better to download the data to the client and then process the data for some business rule you are crazy. The whole purpose of DBMS was to return only the records necessary to accomplish a task and save the throughput.

One other point to make about stored procedures; it is easier to manage security with stored procedures. So you see I can not see why any project would fail with stored procedures, unless you do not understand databases yet and can only do web pages.

We have not even discussed Cobb and Date’s theories of relational databases. I recommend you read and understand their theories before you start yapping about these theories. SQL engines are not true relational databases. What happens if we have to change the locations of data? Stored procedures would only have to be changed where embedded SQL would mean the application would have to be rebuilt and re-distributed.

Best Regard,

Bob Zagars
Senior Software Engineer

I have to ask....why is everyone saying that the alternative to stored procedures is SQL? Has anyone heard of an ORM? I mean, seriously, this is 2006.

For thos .NET people, check out nHibernate, or DataSets directly from MS. For all you open source people (re: java), I am sure you have heard of Hibernate and are probably using it because you don't have you head up your butt!

It seems that this stored procedure debate only happens with .NET people. Why is that? This issue has been settled for years on every other platform. it would be nice if .NET people would join the rest of hte world.

> Has anyone heard of an ORM? I mean, seriously, this is 2006

ORM is the Vietnam of our industry:

http://pluralsight.com/blogs/dbox/archive/2004/10/04/2621.aspx

example:

http://www.kuro5hin.org/story/2006/3/11/1001/81803

I do not know why people say using procs are a headache. If you know what you are doing, it is not a problem. If you work in an environment that processes or manipulates millions of rows, you will know that you need procs in this case.
Also, I cannot imagine putting business rules in a client side app. It may be a good idea to create objects on an application server but if you do not take this approach then you need a database stored proc. Enterpise wide, corporations tend to share processes. If your rules are built into the client then you cant share anything. Reuse across the enterprise is important and not all departments use .NET
You must ofthen think globally across the enterprise.

> If your rules are built into the client then you cant share anything. Reuse across the enterprise is important and not all departments use .NET. You must ofthen think globally across the enterprise.

So putting business rules in stored procs, and tying your enterprise to Oracle/Microsoft/Sybase in perpetuity.. that's a good idea?

How about putting those rules into web services or http REST services that work on any platform? Now *that's* thinking globally across the enterprise.

Warning: rant on database (in)dependence ahead.

Over the past decade or so I've worked with several RDBMSs: Oracle, Postgres, MS SQL, DB2, Sybase, and a little MySQL (wasn't a fan). If there's one thing I've learned is that each database has a fundamentally different architecture, and database independence is NOT easy to achieve. I'm not even sure if it's desirable.

Switching databases has always been a big, big deal in my experience. This coming from a guy who started on the completely opposite end of the spectrum. I did everything generically - this is just the perspective I was taught in college and literature (black boxing and all that). The first way I ever used a database was by querying MS-SQL doing "select * from mytable" and iterating through the results. I realized that was stupid and learned this whole SQL thing. I learned about ANSI SQL and how I should try to conform to this standard to achieve independence. Then I realized I was using the lowest common denominator and achieving poor results. And coding became much more difficult - had to check against the ANSI standard before using a particular feature: "Darn, x isn't 'ANSI'? Well, I'll do it the hard way..."

If you just replace one database with another, plug-in style, you're going to notice differences (perhaps "bugs"), even with the latest and greatest ORM. With the exception of trivial applications, these differences will affect your application, potentially drastically. For example, you have two users running a transaction on the same row(s) of data. Due to fundamental differences in concurrency mechanisms, Sybase might deadlock (and so one user is rolled back and gets an error) while Oracle runs smoothly. Or MS SQL will give you one answer while Postgres literally gives you a different answer to the same query! And yes, I'm talking about setting the same isolation level in your ORM...

If you use DB2, you're going to have to use repeatable read isolation to get a consistent result set. Not the case with Oracle.

Database A may give you non-blocking reads; Database B does not. Therefore your app plugged into database A may run just dandy. You switch to B and your users complain of slowness. Maybe you then conclude that Database B is bad, but really the problem is that you perhaps unwittingly designed for Database A, thinking that you were database independent all along.

Many developers (not all) want to treat the database as a black box - they feel they don't need to know anything about it - they believe they should AVOID knowing anything about it in order to be good object-oriented programmers. The culture supports this notion with terminology such as "data store" and "persistence layer" - just a place to plop down your data and take it out again later, as if it were spreadsheet. In reality, most RDBMSs are very robust with countless features to manage, manipulate, safeguard, and serve your data to many concurrent users.

Why not use the features that make your database special? If you're using a commerical database, you paid a lot of money for these features. Why use the lowest common denominator? Using Oracle's analytic queries I've seen queries run literally orders of magnitude faster than lowest-common denominator queries/code. I've seen an overnight job transformed into a sub-minute job by using Postgres's native SQL that lets you do, through a single query (not row-by-row): "If a row having the matching ID exists, update, else insert".

I find it much easier to rewrite the database API (stored procedures) entirely than to deal with the above issues. I've used ORM in the form of Hibernate, in order to solve the database independence problem (wasn't my choice), and I found the cure was worse than the disease - I'd rather translate my 100 PL/SQL functions to PL/PgSQL (maybe a bad example as they're nearly identical - or maybe a good example as to why it's not always hard - actually both databases support Java stored procedures so we could even go that route...) than deal with 50 Hibernate XML files. And guess what - even if using Hibernate were easier for me (I realize it's an opinion), it never solved the aforementioned problems.

So I typically push for database dependence, aka "application independence" or "using the database to its fullest potential", not database independence. At one job I was told by my supervisor, "all database access must be done through the business objects in order to achieve database independence." He wasted his time writing his own (buggy) security. Database tables lacked all but the most basic constraints, and contained strings of pipe-delimited name-value pairs rather than using native database types. I asked what we will do if we want to hook up Crystal Reports or MS Access or even (gasp) run an ad hoc query from the command line app? Not allowed, I was told! He wanted to be database independent so much that he sacrificed application independence. He was saving his spreadsheet. Thankfully, most perspectives are not quite as extreme...

Think of how many application technologies/languages/paradigms have come and gone. What do you think is important to companies now: Their 12 year old web apps (remember when "CGI/Perl dynamic web pages" was the hot thing to put on your resume?) vs their RDBMS. How about their 10 year old VB/MS Access/C++ Client/server apps vs. their RDBMS? 20 year old green screen apps vs. their RDBMS? I've developed web apps using JSP/Servlets 3 to 5 years ago - I've been almost laughed at for not taking advantage of EJBs. The author of this Hibernate book claims most EJB projects failed and ORM is the wave of the future. You gotta keep up with the times - i.e., scrap the all-exalted paradigm of 5 years ago for the new godsend. So... your app vs. your database - which are you more likely to redesign/throw out within 10 years and which are you going reuse/depend on?

As always, just my opinion. I apologize if the above post isn't the best organized...
Angva

you can't compare apples to oranages!!!

jeff u need a vacation...and yes start using sp.
you shouldn't be debuging your sps if your design is right. have all your business logic in one tier, data access in another, and data retreval in the sp, then you will have OOP and a very easy to maintain application. and if you like web services, that is just another tier on top of ur business tier, and yes it is platform free if you like that idea.

why would u recompile ur own business tier for a sql change? say you forgot one row, or new rows are added in the tables. come on, i would rather just recreate the package or sp leaving the business components unchanged.

and if ur really gonna distribute ur sql queries to the client side, have fun redistrubuting patchs all day =p

Well I have nothing majorly useful to add but I am a developer, stored procs are a nightmare to maintain and I don't like burying business logic in them.

ORM for the win.

I am an oracle DBA. from my point of view there were some benefits for stored procs. like bandwidth utilization. but new servers are connected with GB/Sec lan and bandwidth is no more a bottile-neck. Now things are moving to more distributed env. like grid computing. now there is no point in putting bussiness logic and database in one place and say it is better.
In my experience Stored Procs is the major factor in downtime of the database systems and they don't have measurable benefits.

I do not find writing and debugging stored procedures to be particularly difficult. I am surprised that some accomplished programmers apparently do. While I support the objection to an absolutist policy about using stored procedures, I very strongly favor the use of stored procedures over in-line SQL for the reasons already delineated, but especially the consistent client interface they afford. As for maintenance of in-line SQL in client source code, "Edit->Find And Replace->Find In Files" only works if you actually have access and rights to all of the source files of every application that accesses the database. Personally, I find this whining, harangue against stored procedures to be a type of "Coding Horror" in itself, and from the responses, it appears I am in the majority.

Almost everyone here says either, that using stored procedures is BAD, or, that using stored procedures is GOOD.
After reading the postings, I think, some developers code their complete business logic in stored procedures, while others completely refuse the use of SPs.

I miss a discussion about WHAT should be implemented in SPs and what should not be implemented in SPs. I can benefit from nearly all the advantages of SPs without suffering from their drawbacks, if I do THE RIGHT THINGS in stored procedures.

I think, if SPs are used only to hide the persistence layer's implementation details from the DB-client (hopefully this client is the business-tier), then they are exactly used for what they are good for.

If they are used for hiding business logic from the business tier, then they are exactly used for what they are NOT good for (Anybody surprised?).

I think, that EVERY layer should hide his implementation details from every other layer, and I am sure, names of tables and fields, DB-structure and so on are implementation details of the persistence layer. Stored procedures are a way (maybe not the only way) to hide these details. In my opinion 'classical' use of ORM just hides the implementation of the persistence layer in a dedicated part of the business layer. This is no reason, not to use ORM at all, but in many cases it can be a reason to make well considered use of stored procedures.

As a Business Analyst and amateur coder, I learned a lot from this discussion, but was a bit surprised that no one mentioned transactional integrity (commit/rollback etc).

Sometimes we need for several updates EITHER to work in their entirety, OR for everything to be backed out (e.g. moving money from one account to another). At other times we want several changes to be made visible to other users all at the same instant (as in for example changing a booking from one seat to another in an airline seat booking system).

How would you guys evaluate SP vs inline SQL vs ORM etc. etc. in this area?

An enjoyable discussion, folks.
IMO SP is great for data tasks required by several application modules, and I'm comfortable writing them. Unusual data tasks get buried in C# T-SQL code - which are limited to queries. I use extensive API modules to interface between SQL data and application layer.
It seems some of us are comfortable in writing and using SP, and others in code. Perhaps the best approach is a blend - let each do what it does best in the hands that weild it.

hi there,
what could be an argumentation against following statement (this our current situation):
* DB enforces useage of stored procs for EVERY DB acces
* main reason = they must have the possibility to change/tweak/optimize all DB related stuff DURING RUNTIME without the need of a recompilation or other application dependent activity

=> this leads to a "abuse" of ORM tools where we try to interop with stored procs within ORM tools like hibernate (which in fact is little bit crazy...)

=> but is there any argument against this STRONG requirement from a "almighty" DB section ???

greetings from vienna

The debate have been continued for about 2 years. This is one of the typical software design question that have no absolute yes or no answer. When dealing with this kind of question, developers usually analyze the question in terms of pros and cons, but if we go back and consider the non-function requirement first. It will be easier for us to figure out which solution is better in all situations.
First we consolidate all non-function requirement here, definitely most of them can be achieve with or without using stored procedure, but usually for certain requirement, one solution is better than another. So after we build the full list, we'll have a rule set that can help us to determine we should or should not use stored procedure in our own scenario.

1. Performance
Usually this is the strongest reason for using stored procedure. Without doubt SP can make database operation faster. However it is also a typical remedy to poorly written queries and poorly designed schema. If you believe in the good old 80-20 rule, doing all DB operation in SP is a waste of development effort. With modern profiling tools it is easy to separate slow queries from others. So you just need to tune those queries that really matters. Or wrap them up in SP. On this point, I think a mixed approach is more suitable.
2. Database Portability
Stored procedures are never portable, but such portability is not always required. Many enterprise use one single RDBMS product and never change. Usually, under two scenario this requirement is necessary:

1. You application is a product and your end-users can use it on top of different DBYouYou
2. plan to use a different DB with your application in the future.
3. Security
This is usually necessary if you have more than one applications accessing the same DB, so you may just grant certain application to access SP that it really needs, and hiding the entire schema. However, that means you are using the DB as a point of application integration. It may be the only way before we have other application integration technology like MQ or web service. If you have only one application accessing one DB, and doing the integration outside DB. Why you need SP for security?
4. Service Interface
SP can be treat as interface of service for your application, but now web service is a better way to do so.
5. Unit Testing
No matter you use SP, or data access layer with plain SQL, you have lots of ways to do unit test. So no difference.
6. SP as business logic layer
PL/SQL and T/SQL is not a good language to implement complicated business, better do it in business service layer.
7. SP as a layer to maintain data integrity
SP may be a more nature place for maintaining data integrity. Though there is no big different if you do it in the data access layer.
8. Stop the ripple effect of DB change
Both SP and data access layer can do the same job.

Finally I want to point out another down side for accessing to DB with SP only. Such practice will easily direct developers to produce a too database centric design. And put too much loading to the DB server. As a matter of fact DB usually is a component that is most difficult to scale up. In contrast, the mid-tier can be scale up easily with modern load balance technology. No matter we use SP or not, we can do some trick like cache to reduce the number of DB access. That can also enhance the performance.

Stored procedures are a travesty to software development everywhere.

All arguments for it ignore the fact that any application with a well thought out architecture will have a data-access layer that will make direct SQL calls from the rest of the application unnescessary.

Why write any code that manipulated data in archaic pseudo-languages of sp? Just write a few libraries of code that will act to abstact the data retrieval and update from the database.

There is really no excuse to burn through development time and resources writing and debugging stored procedures. There is no clear advantage to doing this and in my mind represents a huge flaw in architecture.

Object-relational integration people. You don't put business logic rules and validations in the database. Oracle would love to compress the whole N-tier world down into the database engine but it's not needed. Stored procs are useful IF you need to grant individual users security access to specific limits things. However, in practice, most large scale business systems use a generic unrestricted ID to access the database from the app layer and apply access rules there anyway. If the data universe is encapsulated in an object-based access model that hides the underlying relational db structure and uses parameterized SQL to interact with the db engine, that's all you need. Stored procedures then become an unnecessary maintenance annoyance with little practical performance benefit.

The majority of this is simply retoric from MS from a time before there were proper ways of handeling re-use at the database level, that is repeated blindly by the MS SQL masses.

Having a long standing background in Oracle as well as MS SQL, and being extensible capable in both PLSQL as well as TSQL, I can safely say that I have yet to see a single argument in over probably 6 years now, that could convince me there is a need force everything through SPs.

Performance is a load of garbage. Before you people keep repeating this, test it out yourself sometime. Write a program that executes a parameterized stored procedure, and a parameterized sql, and time it as you execute it over a loop 1000 times. Let me know which one wins (hint, it might not be the one you expect, especialy if your querey has any complexity at all).

The fact of the matter is keeping your DAL in your stored procedures is now the lazy part. Get off your butts and write a meaningful API that can have multiple concurent versions, support modern language constructs, participate in actual OO design (as you can write your API in an actual language), access multiple data sources, support distributed and grid models, follow accepted design patterns (abstract factory anyone?) and lead towards a true SOA.

Just because someone told you 5 or 10 years ago that SPs are good doesn't mean they are. We've moved on from that ladies and gentlemen.

Oh, and please people, seperating your database code from your application doesn't make something "OO". (Read the above posts, it happens too often). OO is a form of seperation of concerns, it is NOT seperation of presentation layer :P geez.

heh, I must add, ORM isn't the answer either. Please please be careful with this technology with any sort of mid size or larger project. It looks so promosing to start, and before you know it you're giving up flexibility, functionality, performance and maintainability, and you're not really getting any benefit out of it.

I know it looks so promising up front... but please, just be careful ;)

how to write stored procedure in mysql

Stored Procedures are written in big iron database "languages" like PL/SQL (Oracle) or T-SQL (Microsoft). These so-called languages are archaic, and full of the crazy, incoherent design choices that always result from the torturous evolution of ten years of backwards compatibility. You really don't want to be writing a lot of code in this stuff. For context, JavaScript is a giant step up from PL/SQL or T-SQL.
Stored Procedures typically cannot be debugged in the same IDE you write your UI. Every time I isolate an exception in the procs, I have to stop what I am doing, bust out my copy of Toad, and load up the database packages to see what's going wrong. Frequently transitioning between two totally different IDEs, with completely different interfaces and languages, is not exactly productive.
Stored Procedures don't provide much feedback when things go wrong. Unless the proc is coded interally with weird T-SQL or PL/SQL exception handling, we get cryptic 'errors' returned based on the particular line inside the proc that failed, such as Table has no rows. Uh, ok?
Stored Procedures can't pass objects. So, if you're not careful, you can end up with a zillion parameters. If you have to populate a table row with 20+ fields using a proc, say hello to 20+ parameters. Worst of all, if I pass a bad parameter-- either too many, not enough, or bad datatypes-- I get a generic "bad call" error. Oracle can't tell me which parameters are in error! So I have to pore over 20 parameters, by hand, to figure out which one is the culprit.
Stored Procedures hide business logic. I have no idea what a proc is doing, or what kind of cursor (DataSet) or values it will return to me. I can't view the source code to the proc (at least, without resorting to #2 if I have appropriate access) to verify that it is actually doing what I think it is-- or what the designer intended it to do. Inline SQL may not be pretty, but at least I can see it in context, alongside the other business logic.

When an application is made by a software house (take note!) adhering to CMMI L5 SP choices (why are other not focusing on User-Defined Functions? it is really most taxing to study another language again in the RDBMS) are debated just on the Business Analysis Phase from among other choices. Designs, backward compatibility, languages of choice, technology, RDBMS among others. So any blames will be passed on the Business Analysts and not on the developers.
(document everything)

When an error is incurred within the embedded code of vb or java or c and SQL, the error can not be located since a programming language are not made to check line per line in the SQL statement embedded with it. The enterprise environment of SQLServer can do the same and save the SP/UDF without syntax error. SP/UDF requires specific error handling that may be even longer than the SQL code itself. (document everything)

Codes are not supposed to be rushed, they should be thoroughly tested and there should be separate team just to test the codes. Specific error testing codes should be inserted in between evaluation to prevent unlikely errors. SP/UDF are another language distinct and separate from vb or java or or .Net. SP/UDF receives parameters and passes return values. (document everything)

For me SP/UDF/UDT/Trigger is the only way to go since programming language takes long time to tame and if another problem with SQL adds to it, it will double or even triple the team's development time by more than twice.

Stored Procs -versus- embedded SQL

1. You have a critical, large application that must run 24/7. Whenever a critical production error occurs you have to fix it, redeploy to a test environment, test, and redeploy to production. If the issue/error happened in a stored proc .. you have cut your final deployment time by about 100%. In The Real World "Time Is Money".
2. Changing C/Java/src-Code is inherently more risky than making a change to a stored procedure.
3. The simple matter of testing a stored proc change in a UAT environment is so much simpler, safer and easier than testing a application-code change ... silly to think otherwise.
4. Bandwidth becomes a BIG issue if you have an application with a heavy thru-put and large user community. For example, the need to upload huge amounts of data and process that data should always be done using some native load utility in conjunction with a stored procedure to process the data.
5. Security:
Map all users to a functional id. Map the functional id to an entitlement mechanism maintained in the database and accessible only via store procedures. Associate the functional id to a group that grants execute permission on the stored proc.
6. Guess everyone knows about execution plans, update statistics etc.
7. Distribution of processing and io ...
-----
Thru experience and observation ... will come clarity of common sense.

Response to George..

1) The only time you would save is with the deployment, unless you don't test your stored procedures. However the main time I have seen where this happens is when people used stored procedures for everything; in cases where people put thier CRUD in the code is is alot easier to test and thoses type of situations do not happen. If you cannot catch that a select col1,col2 from tab1 vs the more complex stored procedure during development then your testing is bad.

2) it is only easier if you don't do source control and don't do testing. Otherwise it is far easier to change the code and submit through standard testing.

3) It is only easier because you are not using source control and testing outside of production environment. Nor are you testing that the stored procedure is used outside place that alerted you to the problem. If the stored procedure is being used in mutliple location you better test all thoses locations. CRUD dynamic programming does not have this problem, far easier to test all location since you reused the code it is for the exact reason it was originally created, where with stored procedures you tend to use a stored procedure that gives you what you want even if it gives you more then what you need.

4) While you will save a few 100 characters between sending the query and the name of stored procedure you will quickly lose it because people don't create stored procedures for every case so you will get sent back alot of data that is ignored. For example say you have a table of 15 fields, with dynamic CRUD programming you will just select the fields you need with stored procedures you will generally have 1 stored procedure per table that returns all fields do one request with that stored procedure when you don't need all 15 fields and any bandwidth is gone with just a row or two. Or you could create custom stored procedures for every select you need; have fun with that.

5) Not many people still do individual login account. Most permissions is done against a single account using roles to prevent access to places people don't need to be. Users don't have direct access to the databases and for the few commerical tools that do need this requirement usally work best with access to the tables and have problems using stored procedures for everything.

6) As a DBA I could care less about looking at stored procedures for determining indexes, etc I use the logs to see what is actually being used and base tuning on that. Also since MS-SQL Server 7.0 dynamic SQL and stored procedures are not processed for excution plans any differently. What stored procedure do give you is the queries will be similar enough that the same execution plan could be used; I use a development framework that help ensure that on the code side.

7) CRUD stored procedures are bad for processing. ISNULL and COALESCE take huge amount of CPU vs a straight CRUD command and and also very slow to execute.

I agree totally about the pain of coding in database languages. I am an experienced programmer but relatively new to databases and started experimenting with stored procedures recently. I wrote a routine in T-SQL to determine if a string was a valid IP address or not, and it reminded me of writing BASIC as child.

Maybe I'm just spoiled by scripting languages (Python, Perl, PHP) with regular expression support. It must have taken 20 lines of code to iterate through the string character by character, etc. Something that would take one line of code with a regex.

If you are doing alot of stuff with regex in MS-SQL server there is a decent regex package for version 2000 on code project http://www.codeproject.com/managedcpp/xpregex.asp

If doing it in version 2005 then it is really simple as shown in this example http://blogs.msdn.com/sqlclr/archive/2005/06/29/regex.aspx also the demos that come with MS-SQL server or one of the add on development samples has all the code for doing it.

All the parameter depends on the way the stored procedure is defined and the expertise of the developer in the stored procedure. If you dont use a proper exception handling mechanism, you will be in a mess. Data validation on table data would always be done in stored procedure. It only have a more control over the data in the tables than Java or .net code.

RE Who needs Stored Procedures, anyways?

your attempts to pontificate intelligently do nothing to mask your ignorance.
please head on ; I can make a good living cleaning up the messes you genius's make.

You are SO RIGHT, Jeff Atwood!

I've been reviewing a stored-procedure-laden application for my company... You would not believe how much time is wasted writing, debugging, installing, compiling, and trying to figure out what else is wrong with a (hand-written, several megabyte) package of Oracle procedures.

Let's take the cost of our time writing and debugging these stored procedures and call it X. Lets then take the savings of not writing and debugging these stored procedures (with a substitution of NHibernate or another SQL-less ORM tool) and call it Y. Let's take the cost of processing power (e.g. a web farm) that may be required because the app isn't utilizing the efficiency of stored procedures and call that Z. If Z - Y < X Then it's safe to say that you should never use stored procedures. Z - Y < X has been true in all scenarios that I've encountered.

Sorry , i respect your right to your point of view but...

your mad.

I can not think of one VALID reason for using messy inline code, Not one, using SQL in the db gives you Scalability for one, Security and a lot easier to use.

"can not think of one VALID reason for using messy inline code, Not one, using SQL in the db gives you Scalability for one, Security and a lot easier to use."

There are plenty of good reason not to use stored procedures for your CRUD, just read the longer posts in this topic. However lets look at your reasons.

Scalability:

Yea you can build out your databases servers to increase scalability it is expensive both in hardware and the licenses, far more then to do a web server or client machines. Also once you get into very high end database they recommend you remove the following items for scalability: Stored Procedures, triggers then joins.
Here is a article about the ebay setup from SD Forum 2006 where they say to scale databases remove the stored procedures.

http://www.addsimplicity.com.nyud.net:8080/downloads/eBaySDForum2006-11-29.pdf

Security:

You can make the case for this for client/server program, but there are ways to decrease that. It is even getting less of security risk because companies are requiring secure communications for all connections.
For web and similar applications it offers no security because the user never has any direct access to the database and has no way of access the database.

Ease of use: How?? The tools suck for editing and testing stored procedures even most of those expensive 3rd party ones, maybe visual studio team for dbas is better, have not had a chance to use it yet. They lack the ability to link in with source control, easily switch version, modern debugging capabilities along with the previously mentioned editing and testing tools. What you usually end up with editing outside of the database, submitting the text file, and then running a test script.
From the management standpoint it is not easy to use, you don't know when they are no longer needed, and if you need make an easy change, say the sort order, you need to create a new stored procedures or add a new level of complication to the stored procedure so that it will return different results depending on how it is called; just don't forget to test all the ways it was previously called.

I'm completely on SP's are bad. Here are some of my personal observations.

In virtually every organisation I've walked into where SPs are policy because of 'security' I've seen DBAs, developers and MI experts have complete access to the underlying data so they can run reports, monitor applications etc. etc.

Virtually every DBA I talk to goes on about business logic in stored procedures; HELLO breaking every rule of good programming: your a database not an application the only concern of a database should be storing data the business logic should be in the app.

Stored Procedures break single resposibility (A change in code means a change in 40 sps means a change in the tables blah blah bugs bugs bugs).

Scalability and speed: I have seen some well complex cycles between apps and stored procedures, creating round trips, using temp tables doing some truly horrible stuff that wouldn't be nessasary if you just did DynamicSQL. An example: how about selecting data based on a set of results a get me all records in this list I've just selected. What SP's don't support arrays? What I have to create a comma delimited list and then send it to do the sp which splits is back up into a temp table and uses a cursor blah blah bugs bugs.

Also SPs are Vendor lock-in. Just migrating from SQL 7 to 2005 has created a nightmare for our DBA team and imagine if we went to Oracle: the differences between the features of each RDBMS are HUGE meaning complete rewrites for each database. Is it me or is that just backwards?

Shorter queries: Change the customer name in an object dynamic SQL sends UPDATE customer (customerName = x) rather than having to send every single value of the customer across to the database. I cut my queries down several hundred bytes thanks to dynamic sql.

Security: anyone heard of application roles? You don't have to grant every user access to the tables just the application. Only the application can access the database - surely thats better security not worse.

Every application with SPs I've ever worked on has had 90% of it's bugs, security and upgrade issues in the SPs.

SPs can be good though. Sometimes it's the best way but in 90% of cases databases are a way of saving data for an application to use so why make life so much harder by slowing down development and introducing extra complication by denying the application to choose the best way to access the database.

Another thing.

First the distinction between hard coded inline SQL and dynamic SQL. DynamicSQL requires very little hard coded SQL and with a good O/R tool it is optimized. I can do this: Customer.Get.Where(Age).Is(GreaterThan(18)).Is(LessThan(5))
Rather than:
command.create("sp_GetCustomers")
command.paramaters["minAge"].Type = DbType.Int32
command.paramaters["minAge"].Direction = Output
.... blah blah
resultsSet = command.Execute()
.... loads of code to turn results set into a Customer object.

What looks clearer/is easier to read/change/maintain?

Also how many times have DBAs changed SPs to introduce bugs. And when was the last time that this happened and the DBA didn't use source control. Hell, does a DBA even KNOW what source control is? So even if my SQL is hard coded at least its saved somewhere I can do a diff on it and get it back.

Hello, this discussion is very old - but not yet outdated!
I noticed that no-one answered the question about transactions?
You haven't talked about Linq/ASP.NET vNext much on your blog - I'd be interested by a debate on the subject. From what I understand it could be the 'next big thing' - I certainly hope so.

I firmly believe that code that can be generated shouldn't need to be generated - it should be part of the language/framework. So as the previous poster says - I'm very much against writing embedded SQL, but generated SQL doesn't bother me at all.

I might add that I suspect that a lot of the 'anti' arguments are just a cover for people who dont' like/understand sql. And that the 'for' arguments are DBA's who don't want to share their power!

As someone who wears both programmer and DBA hats, I'm all for stored procedures, for all the reasons already listed above.

I'm from a programming background so I certainly do use source control on all changes to the database. I suspect the percentage of DBAs who don't do version control is smaller than the percentage of programmers that do row-by-row logic rather than set based when writing their SQL code. It's a mentality that can be hard to shake.

As far as transactions go, transactions are transactions. They should be handled either by the dynamic code or stored procedures, but I think that a DBA is going to realize the need more than strict programmers who often aren't as concerned about using all the data-integrity techniques that are already built into a database. I've seen far too many folks reinvent the wheel over and over. Business logic and general data integrity belong in the database.

dietrich ( excuse spelling of name ).
I think that embedded SQL in conjuction with some type
of ORM tool such as Hibernate has its place in any environment.
But that place is very limited in functionality.
I know that we use both in my enviroment and I can tell you
immediately that the SP calls are much more efficient than the
Hibernate usage in a wide variety ways.
1. Less data going across the network.
2. Extreme localization of data base DATA
3. Much Easier to migrate to and MIX various database management systems.
4. Extreme granular entitlement is possible .. ONLY .. thru the use
of stored procedures. Well .. that is a big statement. Of course you can create some complex form of entitlement using a combination of LDAT and Code .. but I find that this is much more complex that using stored procedures to handle data entitlements, taking the burdon away from your Code.
------
I am of the MindSet that the Java/C++ code really doesn't need to know much about the data repository. This could have to do with the business you work in, also.
------
In the case of heavy transaction processes, the Hibernate/ORM tools
are not functional, i.e. much too slow.
----
Of course I am speaking about a professional environment in which
a simple sneeze could cause a major ripple in the world's economy. Thus, cvs, sccs are up the gazoo, not that I really need to say that, along with access to anything with a P in the name could result in not
only an individual's termination but the complete shutdown of a functional group.
----
I see many deadlocks occuring within the groups that do a lot of Hibernate/ORM processing. The code for these hibernate/orm groups contains embedded sql and also they try use Hibernate's own brand of transaction management. I am not sure if that is what's causing the influx of deadlocks.
-----
Also , I am starring to see a lot of "outofmemory" issues with the groups that are making heavy usage of Hibernate/ORMs. Again, I haven't carefully inspected the code base to see just exactly why these types of issues are starting to pop up. I only know that groups using Hibernate/ORM or having more of these issues than other groups.
-----
I am starting to slowly use Hibernate/ORM and maybe I will change my
mind in time about the use of embedded SQL versus stored procedures. But still, I feel that stored procedures are the way to go when it comes to about 99% of database access.
-----
If you look at it this way:

Simple Stored Procedure approah:
Java/C++ ---> Jdbc/rougewave ---> Dataserver(sp,most of the data procesed here) ---> Jdbc/rougewave ---> Java/C++

versus

Hibernate/ORM with embedded SQL:
Java/C++ ---> embedded/SQL --> javabean --> Hibernate ---> Jdbc/rougewave ---> Dataserver(no data processed, all sent back) --->
Jdbc/rougewave ---> Hibernate ---> javabean ---> Java/C++

Now I will say that the underlying code that processes the embedded Sql using Hibernate is very eloquent, having all your returned data nicely mapped and package into a POJO for you. BUT IT IS NOT AS EFFICIENT as the simple stored procedure approach.

But maybe .. after I use a ORM tool ( hibernate ) for a while, I may change my mind.

ps ... anyone here should feel free to visit my webpage ..

I have coded ORMS going back 20 years and nothing has much changed; funny that. The last 6 years I have done more stored procedure work for performance and code reduction on billion+ record scientific databases. Our databases have dozen of applications old and new written to them in different languages and platforms. Business rules in the application would be a nightmare! Since our databases don't have to be portable we take full advantage of a database vendors extensions. I'll give one example: 6 years ago I wrote 3000+ plus lines of C++ code to provide automatic quality control of atmospheric data. Last year I wrote the same application in PL/SQL (95% SQL) in less than 500 lines and it runs 10x faster. For bonehead simple CRUD you can go either way but with real heavy data processing work and statistics etc., I have always been surprised how a database centric design will save time, money, and tons of code. We have SQL here that I cannot even imagine coding in Java or C++ since a relational set orientation to data is just a plain better way to think about the problem then OO; OO is not a decent data model and I have done lots of both.

Just a quick comment. I know the comments are almost inexcusably long already..

Someone towards the beginning mentioned that using stored procedures adds another layer. In my opinion, this is a GOOD thing. It serves as a buffer between the data and the data access tier of your code. If you know anything about OO programming, you understand the need to encapsulate. You don't get this with dynamic or inline SQL.

Not to mention that if you have a schema change or database refactoring, you have to scour your code to see where it is broken. If you use stored procedures, you need only change the procedures to correct the output to what is expected instead of searching through all of your code. If you know anything about OO programming, you understand the need for interfaces and how much of a pain it is when you break an interface. With dynamic or inline SQL, the nightmare is doubly painful. This comes from experience.

That being said, dynamic or inline SQL is not a BAD thing. I am not one of the Stored Procedure Nazis. Sometimes you just don't know what the query will be until you're in the middle of code execution. However, this is more the exception than the rule, and it should be treated as a special case.

My two cents. First of all, I notice that everyone is a bit self-conscious about how long their comments are on this topic. From all that I have read you are all doing very well expressing your views. So vent and feel good about it!

I myself am a Dynamic SQL believer simply for the fact that to get optimal query speed whether it be SP or Dynamic SQL, YOU LIMIT THE NUMBER OF FIELDS NEEDED in your request. If you are using dynamic SQL, you simply Add/Remove fields in the selection. Painless!

When you are a programmer trying to increase the application speed you usually find the problem is a process that only requires 3 fields, but for some reason, the SQL was written as “SELECT *…” (because at the time you thought all of them would be needed). If you are using dynamic SQL in a function called “GetThisData” then all you need to do is change the “SELECT *” to “Select Item1, Item2, Item3”and life goes on. For the die-hard SP followers reading, please tell me the easy way to find and then fix this when using a SP for a program that is not just in use for your company, but sold around the world and every customer has their very own database.

Finally, there is a lot of talk about abstraction in support of SP. What is my business layer for? Just to make another call someplace else to get the data? How many more layers do you really need? And are you honestly getting more done with 2 layers that do the same as one?

someone commented stored procedures give you scability. huh? in a web farm, each server can be doing some of the logic that most developers put in a stored procedure. much easier to scale a web farm than a database server.

everyone has made good points but most of us are smart enough to know there is always a trade-off. i use a combination of stored procedures an parameterized SQL. i use iBatis which is a data mapper more than a full blown ORM. iBatis gracefully handles both inline SQL and stored procedures. i use simple stored procedures to enforce relational integrity, for example cascading deletes. in this scenario ORMs are too network chatty. if there is business logic then i try to do it in code using iBatis' DAO classes or parameterized SQL. two benefits off the top of my head:

1) scalability - the database isn't doing business logic processing for an entire web farm
2) business logic isn't interspersed between our C# business layer and in stored procedures

i use a database for what it is, a data store. not only does it perform and scale better when you use it as such, the code is more maintainable.

Here are some reasons why I prefer inline (though we do use stored procedures here and there, especially for reporting). t

1. Dynamic SQL. Most of the list screens we have in our application include various combinations of search criteria that may or may not be used. Stored Procedures (at least pre-SQL 2005, which I havent used yet) dont let you conditionally change the statement, you either repeat the modified statement for each different criteria) or you do (field = THIS PARAMETER or THIS PARAMETER is null) style coding which I have seen greatly slow down the efficiency of a query when there are many of those conditions. There are places in our Dynamic SQL screens where we would have to write 1000s of Stored Procedure statements to mimic all the ones that can be generated dyamically.

2. Searching your code. When I need to see where fields are being used for update and insert (which we do not do via stored procedures) I can just search the code directories and easily find the places using it). If in stored procedures I have to do one pass to find which stored procedures are involved and then many more passes to find where all these procedures are called. I believe it is far more maintanable to see usage of your SQL right in your code... whether it is in a DB layer or not.

3. Showing your SQL statements. In our ASP (classic and .Net) we call GetDisconnectedRecordset(), ExecuteSQL() or GetDataset() functions (or methods) that wrap around the native ADO code. In doing so, we can consistently trap errors, record out of bounds timings (outside of performance monitoring), and even more importantly FLIP A SWITCH ON OUR WEBSITE and instantly see every query going on to load every page and during every update. That alone is worth its weight in gold. Debuging in many cases is reduced to seconds. And for new people.... you want to see what tables and fields are being used? Turn on ShowSQL and get your answer right on screen!... HUGE!... and possible because its INLINE SQL and all run through common routines. Our common routines also include logic to look for, not process, and alert us to any injection attacks. The "showSQL" functionality when in classic ASP shows up right on the page where the SQL is called (very 2 tier)... in .Net where we have a data layer, our SQL calls write their processing info to a string that we can then get the value of pre-render to display when ShowSQL is on (which is only allowed for developers)... so that is a way to still keep your tier separation, if your app needs it, but still have showSQL functionality. The other thing I can note, with our inline usage, whenever we choose to move a statement to a stored procedure, we turn on Show SQL and in cutting and pasting we are at least 80% of the way there in one shot. We format our SQL statements in code with spaces and carriage returns and on output replace with %20 and <BR> so the statements are VERY readable.

4. Many people dont like to talk about the actual needs of the application you work on. Do you need true Ntier separation? Are you overworking for potential scalability that may never matter? If you structure your app well, it should come down to matters of organization where you could separate to different DLLs and physical machines IF you need to. If you are building EBAY with millions of hits every hour, you have different needs than a company specific app not exposed to the public... with 1000 users and only 100 active at any time. In that type environment, the complexity of splitting your tiers up so dramatically may not yield you much benefit either in development or performance. In fact, the more tiers you have, the more scalable you MIGHT be (if you did it well engouh), but every tier adds more going on in terms of processing. If you read your data from the dataset/recordset and then move it to XML, array, string, etc, and then have to unpack it in the next tier just because you refuse to use DB objects in that tier, imo, that can be going too far in many situations and slowing down your processing needlessly. Calling your SQL within your ASP page code isnt always automatically wrong or due to having weak experience... you can make a case for your situation that it might be the right thing to do (especially if you arent dealing with lots of it and lots of concurrent users). Part of our job is to use the right solutions for the task at hand and there is such a thing as overworking a solution! Same with objects... object oriented isn't automatically better, especially on the web. If you are building robust objects and now your page needs a few things from a few "objects" but now you have to load all of them completely to get your data, that is overkill compared to having SQL return only what you need for the page in a statement (even though that is not an object-oriented approach). I always believe the NEED at hand is the best way to dictate the approach... not a blanket approach for everything. I have done many n-tier systems and many two tier... neither is truly an EVERYDAY right or wrong approach, each can be the right approach depending on the need. Especially on the web, if an area lends it self to business objects... do that... if it doesnt, dont be afraid to NOT use business objects there. Ultimately, efficiency matters... and in many cases inline SQL right in the page is going to be more efficient and responsive. As someone said, inline vs stored proc is always an evaluation of pros and cons... both from developer viewpoints, and from the needs of the application. What is right in one case might be wrong in many others, but it doesnt mean each approach itself is always right or wrong.

5. Maintenance. If every SQL call in a large app is in stored procedures, I think you have a huge mess. It's alot to look through... and SQL Server (or most database tools) do not give you much in terms of choices in organizing. SQL 2000 and before (again, I havent used 2005)... all stored procedures are in ONE directory and you only control the naming. Even though you can use .SQL script files to initiate changes (and get into source safe) (which we do) it still becomes a mess juggling all of these... especially when some are so simple they hardly are worth the bloating of the DB objects.

6. Bugs due to stored procedures separation of statements. If you add new fields to a table and thus have to change insert and update stored procedure to accept these fields, but have 6 apps using it. As soon as you change the procedure, you better have updates for ALL those apps in place or else they will all break on the change. If you add new fields and use inline code, each app will generally work fine with the new fields there (if they allow nulls) and you can then work in changes to use those fields by updating those apps one by one all the while the prior version still runs (and there are cases where this is useful). Again, its relative to what your system does, who the users are, and how its rolled out, among many other things.

6. We use Crystal Reports... and the last thing we want is Crystal controlling the queries... so there we use stored procedures that Crystal Reports will work with.

7. We have a couple cases where we couldnt run a statement inline (too complex: many joined tables, group by clauses in statements acting as tables within outer queries that are also grouped) and moved it to a stored procedure because that made it run. Some would say, you shouldnt write such complex queries. I say, in relational databases, where you have many tables involved in the answer (this is what normalization leads you to often) if you can write a statement that is VERY complex but runs in 2 seconds compared to one that is made up of many simpler statements but then takes 40 seconds... I lean on the speedy approach!

For me, comparing inline SQL to Stored Procedures is like Comparing Manual Transmissions to Automatic. I think Automatic can isolate you from many things, but in the end, there so many more pros to the Manual transmission that Automatic cant hang with... and thus, thats also why I choose inline over Stored Procedures. It's not a product of being "lazy" at all, it is that there are many overwhelming advantages that taken altogether make it, for me, the best choice... IF YOU USE IT SAFELY AND WISELY and focus on efficient statements and injection protection. I also hate the notion that you have to isolate the DB from your junior or UI only developers. If that is how you run your business, imo, you run your business wrong. If developers cant handle DB work, they simply shouldnt be developers! Developers need to be encouraged, trained, and expected to handle DB coding and learn how to write efficient SQL Statements (whether inline or in stored procedures), they either have to learn or should leave the profession. Having DBA handle all that and isolating the database from the developer is not an approach I favor. Having DBs review things, look for problems, and help developers, on the other hand is just as valuable as having people who do nothing but test the application!

When applications get too rigid in rules and layers and layer separation, I find very often functionality is skipped because its too hard to do given the structure of the app and the layers. That is a shame, because ultimately, a system is used at least 90% of the time to make someone's job faster and easier... so the tools and techniques should not routinely impose limits on that goal!

Finally, inline SQL does not equate to crappy sql. You get way more bang for the buck in learning the right techniques to speed up your SQL Statements (hit indices, use joined table results as its own table as if the results were a single table and then join to those results)... and those techniques can produce amazing results whether inline or stored proc and they are the more important bang for the buck. Crappy is independent of tools and techniques. You can write ASP with inline SQL that is very clean. You can write .Net with stored procedures where its a mess. The nature of there being a mess is not driven by or limited by the tools used!

(Sorry for the length, but I unless I missed something, I think I added some significant additional thoughts here).

Too often, I think people use a certain tool or technique because they were told that was the way to do it... and they believe it like a religion. Think deeper than that... there is place for just about everything.... stored procedures arent always wrong... inline SQL is certainly not always wrong... but how you use that mix really depends on your application, its purpose, and the size and needs of the user base. I have yet to work on any application where stored procedures for all queries and insert or update statements would have been the best approach and in every case I would have greatly missed the benefits that inline SQL provided in comparison!!

"Anyways" is not a word. The correct usage is "anyway." You wouldn't write "anyhows" would you?

Lets put things in perspective. I work at a place where users build asp.net applications. They stick SQL wherever its convient and they spit out a working application in 2 weeks. Does inserts, updates and all the things that a lot of you would pat yourselves on the back for.

Here's news for you. Cracking out apps without any regard for good application design is is not rocket science. You don't even have to be that intelligent to do it. I could teach just about anyone basic programming concepts like conditional statements, for loops and basic SELECT and Insert statements in a couple of weeks they'll be writing applications that are just about as good as a lot of "professionals" out there.

Without going off on a tangent, calling stored procedures from your code behind or windows form is not a good idea either. There's something called a "business layer" which is used to abstract your "data layer". Look it up.

Oh and how many of you actually have had to access databases from different vendors anyway?

So instead of defending putting your SQL Queries Inline, why don't you crack open a book on application design and learn something that might separate you from some guy in India with a "teach yourself SQL in 5 minutes" book making 4 bucks an hour.

> You wouldn't write "anyhows" would you?

Maybes I would.

What a load of bollocks. I completely agree with Mike C on this. SPs make code more maintainable, increase performance and help when implementing N tier applications. It also allows you to share business logic between the database and the business objects. You probably write loads of nasty code with UI logic mixed up with business logic. And the argument about SP compatibility, initially this can be overcome by creating new SPs with structured names to indicate version or by simply setting arguments to default values and ordering them correctly.
Try Rocky Lhotkas CSLA.NET site for some rock solid techniques on how to implement data access and business logic using his framwwork.
While you guys are flaming use of English (not American) what does the phrase "Go Figure" actually mean. Its not a sentence is it. Surely its "Go and figure it out yourself" is the correct use!

looking through most of these comments, it's almost as if people don't know you can use default values for stored procedure parameters that aren't specified at run-time.

My two cents:

I’ve heard this about stored procedures:

1. They are more secure. But... in every database I’ve seen, the database user has dbo access. BTW, I work in a large bank. Go figure.
2. You can debug them as easily as .NET code. But... I’ve never seen anyone set up SQL debugging in Visual Studio. Most developers run the SP and hope it works.
3. They can be version-controlled. But... try putting them in SourceSafe and see if you like it. Honestly, can you tell me how this SP looked a year ago? What were the last 5 changes to it? Who made them?
4. It’s easy to control schema changes. But... if you’re planning to rename a table, is it easier to hit Ctrl-F in your code and search (or search VSS), or to assess this impact on SPs in the database? Sysdepends is great, I know. I guess I could just script the entire database, dump it to Notepad and look for the table name. Sure.
5. They’re faster. But... nobody seems to be willing to prove it. And even if they are, nobody cares that an SP is 10ms faster if it’s 10 times harder to maintain.

LINQ, anyone?

This debate has been going on for how long, almost 4 years. As I said in the past I have been developing database application since the late 80’s and electro-mechanical devices since the mid-70’s.

We forget that we deal with 1’s and 0’s which running in resident memory is always the fastest method. Anytime we transfer that information via storage or network connections it slows down the process. So I am not advocating only stored procedures, you have user defined functions, views, triggers, and other parts of a DBMS to compile the data to be send back to the requester.

Now companies in the 90’s to make budget developed the easiest methods and many times they scrimped on quality (who won Beta or VHS?). Does not mean it is the right way to develop applications. Also because of market shares and business the best methods are lost to marketing and who has the money.

Still I content that front-ends and data manipulation must be separated to be a successful system. In long term managing of data it is the safest method. Microsoft Visual Studio allows several technologies to protect your business presentation layer from the user interface. Remoting or web services are two technologies to keep your business presentation layer separated from the UI, and ADO.NET controls data concurrency with industry databases.

Now I do think Microsoft or one of its development partners should develop a debugger for SQL that works within Visual Studio. I know the technology exists and have used it successfully in the past. It does need two parts one that resides on the database server and one on the client development computer.

Anyway stored procedures have a valid part within our IT environment. As developers we have to decide on response times and manageability of systems, also availability for the specified business. Some systems were built on limited technologies, it does not make them wrong but they could be improved upon.

Mike C: "I could teach just about anyone basic programming concepts like conditional statements, for loops and basic SELECT and Insert statements in a couple of weeks they'll be writing applications that are j