When to do stored procedures and when not to

This is just a general discussion on what is the best occasion to use stored procedure! Personnally i have very low opinion on stored procs becouse; 1. they tie you to one particular database enviroment and 2. the idea of shuffling back and fourth between your interface code and the back-end enviroment for the stored procs its a nightmare for me, i prefer sticking to one enviroment when coding.

Having said this i have my coleagues who are madly in love with stored procs even for very small applications, i need a way to convince them that stored procs are not the best solution to every problem.

Guys what do you think of stored procs?

From stackoverflow

• I agree with your comments about swapping coding environments and being tied to a specific DB, but there are some really good reasons for using SPs:

  1. Security: in high/properly secured systems pretty much no-one has direct access to the tables, including the admins. All access is via SPs which do have permissions, in that way all access can be monitored and controlled. You just can't do that any other way.

  2. If your code talks to SPs then the underlying DB structure can change without your code having to. Seperation and loose coupling is a real boon, especially when upgrading, it's common to write new UI code to talk to SP's prior to upgrading the legacy backend

  Craig : I am a bit skeptical about point 2. I have never seen a case where the DB structure changed and it didn't effect the application other than for trivial changes.

  Giovanni Galbo : I wish I could +1 Craig, he makes a very valid point.

  annakata : @Craig: I've actually seen it work in your favour a lot. This can be simple things like the expansion of a table into multiple tables, or a union with new related tables. Abstraction like this is very rarely a bad idea in itself.

  Jon Artus : @Annakata: Absolutely. In theory, you can entirely rewrite the database and the consuming application shouldn't care (if your abstraction doesn't leak, that is). It's the same reason you'd use interfaces in OOP.

  Kezzer : +1 for loose coupling. We use SPs at work all the time to perform advanced operations, or for large queries, or for things that need to take a set of parameters. We've got thousands of SPs.

  Eduardo Molteni : +1 Craig. In all my years of software development never have to confront with a single case when I need to modify the DB but not the UI or Business logic classes.

• I think they're nice for poking around in the database manually (they are useful for quickly checking things etc), but don't use them for production (for the reasons you mentioned). Use server side business logic instead.

  Mark S. Rasmussen : Why would you use an SP for that? That sounds like ad-hoc querying through management studio / similar.

  tehvan : You don't always have the right admin tools to do certain things.

  Jon Artus : I think the advice "Don't use them [stored procedures] in production." is a very general (and incorrect in this case) statement and possibly needs more explanation...!?

  tehvan : @Jon: updated my post. Can you explain why it would be incorrect?

  Jon Artus : @tehvan: I'd always go SP-based in anything but the most trivial apps. The benefits in terms of security, abstraction and performance far outweigh the costs.

  S.Lott : +1: Code is code and belongs in the application. Data is data and belongs in the database. Stored procedures are code. It's just confusing to have code in two places.

  Guy : @slott: Data logic should be as close to the data as possible. The way you implement a feature in records and fields should be managed by sp's. APPLICATION logic (why your doing something) can be in higher tiers (the application / GUI) but the GUI should not manipulate the db directly.

  HLGEM : Gotta agree with Guy. Databases are accessed through far more than the UI and the code should be availble to all. I can't even imagine trying to manage change to a database when all the code is in the UI, What a nightmare!

  tehvan : @Hlgem: Of course there shouldn't be any db related code in the UI! That's what the DAO layer is for.

• You have to admit some stuff it just makes more sense to do in a stored procedure. It's true that a lot of the stuff that people choose to implement as SPs can really by done in code (that is, in your application's code), but still, if you need to do some administrative work that goes over a whole bunch of tables and repeatedly run some task a SP usually makes more sense.

  annakata : the point about app code *should be* unrelated. The DB query should be about marshalling data, nothing else and definitely no business logic, but equally the APP should only be concerned about asking for the data, not worrying about exactly how the DB implements that.

• If you are writing queries against your DB rather than using an ORM, and these queries need to interface with business code, then stored procedures is the best place to do it.

  SP's provide an excellent way of standardising your database access. If all you ever do is very simple one table selects then it might be ok to give them a miss, but if complex logic is needed in the database a stored procedure is a great way of standardising this. Plus you get all the syntax highlighting that a good editor provides, and you don't need to worry about escaping strings and stuff like that.

  I don't really like the argument that it makes migrating to another DMBS a pain. Migrating to another DBMS is always going to be a pain if you are doing anything of complexity in the DB, and who really does this on a regular enough basis to allow it to influence something that they do on a nearly daily basis (write queries against the DB).

  Mr. Shiny and New : how do stored procedures help with escaping strings? the call to the stored proc can still have SQL injected

  Jack Ryan : I wasn't talking about injection. I was talking about the fact that placing SQL inside c# strings for example can involve a lot of escape characters. With regards to injection in stored procedures, a properly parameterised SP will prevent the most determined malicious user.

• If the database and its embedded logic is shared you're DRY when you use stored procedures, because that way all clients of the data use the same rules.

  If there's one, exclusive steward application, as in a service oriented architecture where a single service owns its own data, you can put the logic in either the service or the database.

  Some DBAs prefer stored procedures because they feel that they act like an interface to the underlying schema.

  I think the DB migration argument is overblown. It's seldom done, in my experience.

  Middle tier apps come and go, but data is forever. Those are the crown jewels of any business. I think it's best to do what's necessary to ensure the integrity of the data, and stored procedures can be a good way to do that.

  annakata : All good points, but especially the DB migration. I've never seen or heard of anyone actually doing this in 8 years.

  duffymo : Agreed - I wrote "seldom", but the right word is "never" based on my personal data. Once you buy Oracle or SQL Server, that's where you tend to stay.

  Matthew Watson : Database independence is only really needed for vender applications, where you have no control of what database its running on. Anything "internal", you would be crazy not to leverage the advantages of yoru chosen database for this fantasy of database independence.

• Oracle's Tom Kyte - "Ask Tom" - has written something like this for Oracle: "if possible, do it in one SQL query, if not do it in a PL/SQL stored procedure, if that's not possible do it in a Java stored procedure, if that's not possible do it in application code". That's rather extreme, but I can see some general pros for stored procedures:

  • Easy access to the database, it is simply easier to do some things closer to the database
  • Performance, not having to send a lot of data back and forth between database and client or application server

  and one general con:

  • Much harder to keep SP code in sync with source code control
  Matthew Watson : I really fail to see how stored procedures are harder to keep in source control than anything other source. Keep your stored procedure/packages (everything should be in packages anyway) in a text file, check that into version control... no different to anything else.

  duffymo : @Matthew - exactly right. I agree. Database artifacts ought to be controlled just like middle tier code.

  Mr. Shiny and New : @duffymo: I agree. But often the DBAs are a separate team from the developers and they disagree. sigh.

  Nils Weinander : Matthew - the difference is the lack of direct source code repository integration in the database. With a text file in version control you still have that extra step of updating the procedures in the database. Preferrable would be version control integration in the database.

  ObiWanKenobi : "Much harder to keep SP code in sync with source code control". This is a big lie. Harder than what? What's so difficult about checking in a text file with the stored procedure text? If I want to, I can go directly to the web server and change the code of an ASP or PHP page. This is a process problem, not a technology problem.

• Basically, I'd use SP if

  1. You are coding against a database which has good support for SP's (eg Oracle)
  2. If you are confident that the applications connecting to the database will change more often than the database, eg, "Application independence" is more important than "Database independence"
  HLGEM : excellent point about application independence!

• A couple more advantages of SPs:

  • You can filter in the DB by using query parameters, rather than in the calling application. This allows you to return much smaller result sets from the DB, saving on both time and bandwidth.
  • Using query parameters rather than just concatenating SQL statements is a great way to guard against injection attacks.

• In essence, SPs keep SQL code on the SQL server.

  You're writing SQL in its native environment:

  • you'll be using tools designed for working with SQL (query analyzers, optimization, auto-completion, diagrams)
  • DBAs are happy & productive living in this space

  SQL code won't appear in the normal code-base:

  • large opaque blocks of text in strings don't really add value to the coding experience
  • conversely, not having these large opaque blocks of text in source means they don't go into source control which would be a good thing (it is a pain to manage SQL schema versioning, even with handy tools like Redgate SQL Compare)

• One of the most important reasons to use SPs is data integrity. It should be up to the database to maintain its own integrity, rather than the client. Imagine the case where you are deleting a customer with an address. This, with a property normalised database, would involve deletions from at least three tables: customer, address and customer-address (a join table). You should not have to rely on the client to remember to delete from the join table - there is too much risk that you will end up with orphaned rows. You should therefore call an SP that performs the deletion from all three tables in a single transaction. Database integrity applies to all inserts, updates and deletes, therefore I'd recommend using SPs for all DML commands.

  On the whole, I'm happy to query directly from the client, except in cases where you cannot be sure in advance how many queries will be necessary. For instance, if you want to get all of the payments made to an account, but for some reason (due to the data model), you are unable to get all of the data that you need in a single query but instead have to do a query for each payment, then the client is going to be making multiple queries to the database; multiply this by x concurrent users, and you end up in a mess. In these circumstances, it is best to use an SP, therefore requiring only a single interaction with the database.

  EDIT SPs also allow more flexible code reuse. Any application, written in more or less any language, can use the SPs. Furthermore, if this is a client database, then if you write proper interfaces (and documentation) to these SPs within a secure schema, the client can write there own applications to interact with the database.

  EDIT SPs are also, possibly, the most secure way of preventing SQL injection

• I used to be a stored proc fan, but parameterized queries can work also. Just don't use in-line SQL -- it leaves you open to SQL injection problems.

  The performance differences are trivial.

  Many ORMs like NetTiers and LLBLGen use paramerized queries.

• Count me firmly on the sp side. It is much simpler to use sps for acces than any other method. You make a change, you can upload it to prod without having to recompile your UI. Yes there are times when you have to change both the UI and the proc, but there are many,many times when just the proc needs changing.

  Security is better on procs unless you use dynamic sql (which should be avoided at all costs as it is impossible to fully test and less secure). This is becasue users do not have to have direct access to the tables and can only do what is specified inthe proc. Not only does this help in outside attacks, but far more importantly, it makes it much harder for the disgruntled or greedy employee to commit fraud or destroy your data. Any financial application or other business critical application that does not use procs is at risk for serious theft or destruction.

  Performance tuning is another area where procs can shine. It is much easier to performance tune a proc then upload the change, than find the code in the UI, figure out how to make it better and then recompile and upload the UI code. ALso if multiple steps are involved, the proc just works better to begin with. DBAs, who often only have access to the database code and not the UI code, are generally far better educated on performance tuning a database than the application developer, so making the code easier for them to work with is also a priority. Some tools like LINQ to SQl which create the code automatically are a nightmare to performance tune (and they do not create highly performant code in general from what I've seen). I wouldn't allow anyone who touches my database to use such a tool as performance is critical to databases.

  It is also easier for design teams to determine the effect of a potential change to the database when only procs are used. That way they don't have to search the code base for several different UIs and all the backend database stuff like SSIS packages to find what will be affected. This makes it far more likely that a database will be refactored to imporve performance or utility.