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

My company has decided to use stored procedures for everything dealing with the database (because they didn't know of any other way besides raw SQL), and as the saying goes "When in Rome..." so I try to follow. Recently I had to add a hack fix that required grabbing a database value, and since it was a single value from a single table I wrote it as inline SQL (parameterized, of course) since there didn't seem to be a need for a stored procedure for one trivial line of code used in a single part of the application as a kludge.

Of course, I've now been told to fix it and only ever use Stored Procs for anything related to the database. This feels just a bit too much like blindly following dogma instead of using common sense. Don't get me wrong, I understand the purpose of having coding standards but I am also a proponent of ignoring standards when they don't make sense, not just blindly following them as though they were Gospel.

share|improve this question

closed as primarily opinion-based by MichaelT, Bart van Ingen Schenau, Kilian Foth, jwenting, GlenH7 Sep 18 at 13:35

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise.If this question can be reworded to fit the rules in the help center, please edit the question.

1  
I think you're right, its sounds like they're being dogmatic about it. Particularly for just a read, that seems like overkill. –  GrandmasterB Apr 13 '11 at 18:05
11  
Perhaps they don't trust their developers to check for SQL injection. Perhaps the SQL login they will ultimately use will only be allowed to execute Stored Procs instead of running SQL queries directly against the database. Perhaps they want a 3rd party DBA to review all sql code for optimization and prefer DB queries be in the DB, not in the application. You'll never know unless you ask. –  Rachel Apr 13 '11 at 18:14
2  
Have you tried asking "Why?" If they can't articulate a better response it may be a good opening for discussion. –  TGnat Apr 13 '11 at 18:25
2  
@perdian Where'd all the rum go? –  Wayne M Apr 15 '11 at 12:05
2  
Just you wait until somebody has to spend hours to find out that you took an unauthorized shortcut. For bonus points spend hours to find out that somebody else took an unauthorized shortcut. Your code is doing something unexpected - don't do that. –  user1249 Sep 21 '11 at 11:26

11 Answers 11

up vote 16 down vote accepted

Code standards are typically just guidelines. However, it sounds like your company has a policy and policies typically can't be ignored. If you have brought it up and have been told to use a Stored Procedure instead, then I would go ahead and do that even though I would make a different decision if I had the authority to.

share|improve this answer
1  
I'm going to, just to keep things consistent. Just a point of frustration because policies that serve no real purpose are silly IMO. The reason was literally "We use stored procedures here." –  Wayne M Apr 13 '11 at 18:10
    
@Wayne, I agree doing thing because "That is the way we do things", is always a poor reason. That is normally when I question other solutions and seek to change the rule. Sometimes I succeed, while other times I end up giving in to the person who is ultimately responsible for the project/team. –  jzd Apr 13 '11 at 18:40
5  
Carefully balance the ROI of challenging the standards. If spending 16 hours in meetings, drafting process change documents and memos, and staring at your monitor frustrated (that's 12 of the 16, btw) saves you 10 minutes of development, you're at a bigtime loss for the firm. If those 16 hours save you 10 8-hour projects (like writing tests on constantly buggy code, for instance), that's very different. Remain practical! –  corsiKa Apr 13 '11 at 21:44
    
@corsiKa: I fully agree, but there is a problem. From the first few encounters it's difficult to see how often it is going to come up later. –  Jan Hudec Oct 13 at 12:06

The only place I routinely ignore coding standards is in autogenerated code, otherwise it is usually handled on a case by case basis and double checked in a code review. You can't be a slave to coding standards, but exceptions are pretty rare in my experience.

share|improve this answer

As a long time coder and team lead, I must be able to think outside the box. Coding standards help keep things nice, but when they get in the way, there can be hell to pay. In this case it depends on their definition of procedure. If it is restrictive instead of permissive then it is up to the leads to show where there are problems.

share|improve this answer
    
It's not up to the leads to show the problems the standards solve; it's up to you to show where the standards actually cause problems. I can't remember any time where the standards where I worked caused me a significant amount of trouble in doing something, even when I was thinking outside the box. –  David Thornley Apr 13 '11 at 18:34

If you don't use stored procs then your dbas will make data structure changes without knowing what impact they might have on your inline code that they don't know about. It is a major problem for maintenance when a cowboy coder doesn't follow the design. This is not about coding standards - this about the design. You don't always have to like the design or want to follow it, but it isn't your call, so just do what you are asked to do. I'd give you one free pass on something like this and then fire you.

share|improve this answer
    
We don't have DBAs so this is really a moot point. –  Wayne M Apr 13 '11 at 18:50
1  
Ignoring the design decisions means you cannot be trusted. –  HLGEM Apr 13 '11 at 18:52
    
Strange way of thinking; "design decisions" are gospel from Mt. Sinai. I guess we agree to disagree. I have no qualms about ignoring design decisions that keep code cluttered and unmaintainable (not necessarily the use of sprocs) versus writing clear, concise and maintainable code. –  Wayne M Apr 13 '11 at 18:53
1  
@Wayne M - design decisions are not gospel; they can be changed, just not by you. Your employer may not have any qualms about ignoring your paycheck. We are all free to live with our decisions. Find a better battle. –  JeffO Apr 13 '11 at 19:42
    
In spite of the downvotes, I go +1 for HLGEM - he's got it right. You're getting a paycheck to do what you're told - you want to follow your own standards, open your own shop... –  Vector Sep 7 '11 at 18:50

It might at first seem overkill to be so rigid about guidelines like this but I think it's important to stick to guidelines unless you've a very good reason. I say this because of the broken window theory . This is particularly true if you have junior developers who still need to the good practices and habits drilled into them.

Is the fact that your fix was quick or a hack really a good enough reason to break a window?

Consider an inexperienced developer maintaining your code, they perhaps change the query or add another one in a similar manner and suddenly there's a security exploit because they didn't realize that changing the query required also changing how it is executed.

Note: It's a separate issue whether the guidelines are things that should be always used in the first place, but that's an issue to consider when setting the guidelines. My point is that once you have settled on things that are always good it's important to keep to them.

share|improve this answer

Code quality can be measured by its readability. What you want is to look at code and see what it does.

The whole point of coding standards is to enforce readability across a team, because you want to look a colleague's code and see what it does.
Ideally, this leads to code, that everybody can read. It's like expecting people to speak clean English instead of mumbling about with their own accent and to write with a decent level of spelling and grammar, instead of writing everything in lolcat- or leetspeek.

Now what your company conceived as a standard doesn't enforce readability across a team, it rather reduces it. For every query made to the database, you have to lookup the stored procedure.
This is like expecting people to instead of saying normal sentences as "Would you like coffee?" to say "You have an e-mail with the subject 'Coffee' in your inbox" for normal communication. It doesn't increase understanding across your team, because the stored procedure (or the content of the e-mail) could just be complete mumbo-jumbo.

So it is not a (sensible) coding standard, but rather just a stupid formality. The only point of stupid formalities is, that they help limiting the amount of bullshit a person can create per time, but they get in the way of people who have an actual contribution to make.

You should try speaking to whoever is responsible for that (and be a lot more polite than me ;)).

share|improve this answer
    
I would LOVE to get away from stored procedures, trust me (I think they have benefits, but are usually overkill). The code is so reliant on them, though, it would take nothing short of a full rewrite to switch to an ORM and that will never ever ever happen. –  Wayne M Apr 13 '11 at 18:50

I think they are actually trying to separate the contract between the app code and the DB. Therefore, if they ever needed to change a column name, for example, they would only need to make sure the contracts (SPs) work.

Get_Costumer(), or whatever, is a way to abstract the app code from the structure of the DB and it is, in my opinion a real best practice you should consider following. Achitecturally you almost always want your DB and app code to be decoupled.

share|improve this answer

If you just can't make it work, I think you will have found your exception. At some point, the team identifies that doing it within the context of your standards, just takes too much effort or produces a poor solution, you make a documented exception. You change the standards when this starts to happen too often.

You're only using this as an example, but is wrapping a select statement in a stored procedure really that difficult? You obviously get a lot of practice in your shop. There are other standards that are probably more difficult to follow than this. I don't know why programmers wouldn't prefer to pass this off to a dba (I know, not your case.). Personally, sql in most programming ide's looks like crap, but like everything else, you get use to it or start using ORM.

share|improve this answer

It is always OK to violate coding standards; however, when you do so you should always write a comment mentioning that the violation was deliberate, and provide some sort of justification.

share|improve this answer

RECAP ON OTHER ANSWERS

Here is a quick summary of what others have said here before.

Pro company policy:

  • Allows for tracking dependencies between database objects and overview how planned changes affect the schema;
  • DBA team is able to review and control application access rights;
  • DBA team is able to predict performance impact of changes;
  • Decouples database from application code;
  • Broken window theory... meaning that's the way how they organize their code, and breaking the consistency will makes the infrastructure harder to grasp for newcomers, which will make them respect and strive for quality less;
  • You get your paycheck to do what you were asked to do. This is a company policy and right now you are not in the authority to change it, so better live with it.

Contra company policy:

  • This company mindset is very rigid and dogmatic, and the policy makes developer's work unnecessarily cumbersome. Unfortunately, see last point in the Pros section;
  • As back2dos refers to it by saying "For every query made to the database, you have to look up the stored procedure", a sprocs-only policy often results in code duplication, because different developers have no idea which sprocs could be reused for their problem at hand;
  • Also, ironically the contrary to the previous case creates problems too, when some application owns a sproc, then another reuses it, then the first one updates it without the knowledge of who else started relying on it. DBAs track the dependencies not further than the walls of the DB server room, so don't expect them to give a damn what app relies on what outside of that. If the application teams don't track it between themselves that's their problem, the DBAs are covered.

THOSE 2 CENTS

Firstly, many answers here use the word standard. The practice of prohibiting direct queries and only allowing sprocs is not called a standard. It's a policy (see jzd's answer).

Secondly, specific to your problem: my main contra argument against such a restrictive policy of using stored procedures exclusively would be the SQL language itself, and not necessarily the centralized business-logic repository infrastructure it promotes (though that has it's counter-arguments too).

SQL is a rather rigid and non-composable language, with quite limited expressive power. This means that you will hit a dead-end very early with regards to code reusability. One of the reasons of this rigidity is that there are no means to pass first class functions in any way (like with OOP languages using polymorphism), which limits composability significantly. The closest you can get to that in expressive power is by writing dynamic SQL queries constructed using string concatenation. Not a neat thing. Dynamic queries defeat some of the points in the "pros" section, like the tracking dependencies between DB objects, and usually have worse performance, error prone, hard to debug, and increase the risk of SQL injection attacks. Unfortunately, with SQL you'll find that you can't get very far with extracting common reusable logic between sprocs without hitting the wall and being forced to resort to dynamically executed queries.

UPDATE: Another big limitation of stored procedures, besides the first class function thing, is the passing and returning of composite data types as arguments, whether it be lists, sets, records, or key-value pairs. This hurts composability badly too.

Finally, I don't necessarily agree with one of the pro points above, the "decoupling DB from application" by Jorge: The main principle which I feel applies here is to prefer primitive data structures with large set of common reusable and composable operations, rather than working with custom APIs. Sprocs are such custom APIs here, which stand in-between the user and the primitive relational data to query it using common composable data manipulation primitives (select, join, where, group by, etc). Now SQL itself is not the ideal choice to be the DSL for composable data manipulation primitives, due to the above mentioned rigidity, but with a more sensible language choice (like .NET Linq... or Lisp/Clojure!) you could run your logic against a simple List just the same way as against a DB ResultSet. Obviously, that makes it easily testable, which is a Good Thing. I say prefer your data-store to be dumb simple and primitive, such that it can be stubbed with plain CSVs. As you see, this model decouples DB from application too, only it draws the line at a lower level of abstraction.

WHAT TO DO NEXT?

It's a bit unrelated to the question, but I encourage you to take a look at Datomic, which has an interestingly novel approach to storing and querying data, in line with some of the above observations. (Obviously, I mean look at it strictly outside the work environment first... definitely do NOT go to the CTO's office the next day and say "Hey guys I rewrote some of your sprocs in Datomic and deployed it on this shiny prod server over there, it's really cool take a look!" They might not appreciate the otherwise completely understandable excitement ;)

share|improve this answer

Standards can be ignored in the following circumstances:

When you have talked to your fellow developers and obtained agreement or you have set in place a policy is intended to be enforced or you your business decides that not following standards is the correct business decision (for example millions of dollars might be on the line for a fix "now" regardless of whether it conflicts with standards).

This will apply to rules if;

  • they have been ignored and abused to the point where a large portion of the code doesn't follow them.

  • there are several competing sets of standards, and it is not clear which to apply.

  • the local standard go against the industry standard, e.g. a company say we use 9 spaces to indent (!)

But even in the above 3 examples the GOLDEN rule is, whenever possible, you speak to everyone concerned FIRST. At the very least (e.g. 2am fix) you should discuss your decision as soon as feasible - and discuss it, not just send out information about what you did. Be prepared for criticism and to make changes.

share|improve this answer

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