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.

Can you think of some reasons that the management force the developers to write and call Stored Procedures instead of inline SQL statements directly?

Even a very simple CRUD statement, writing a stored procedure takes more time and create extra workload.

share|improve this question
3  
I suggest you give this a read: simple-talk.com/sql/t-sql-programming/… –  Tyanna Oct 11 '12 at 4:10
    
I really suggest you do a search in this site and in StackOverflow site, amongst others of course, as there is a lot of discussion on this subject. I have a feeling that if you know how to write stored procedures, you would accept using them without a problem. –  Emmad Kareem Oct 11 '12 at 5:08
    
If you want to know why your management forces developers to write stored procedures, it's probably better to ask them than for us to guess as to their reason. –  Walter Oct 11 '12 at 11:58
add comment

2 Answers

up vote 2 down vote accepted

One of the reasons might be due to the fact that old habits die hard.

In older databases, stored procedures used to usually perform better due to caching of the query plan for stored procedures in comparision to ad-hoc queries which did not usually get cached. A lot of newer (i mean databases in the last 5 - 6 years) versions of databases do provide caching of queries too - alleviating this drawback to a very large extent

Another possible use for SP's could be optimization. When you have SP's for every query, it is possible for DBA's to tweak the queries if needed to improve performance. If the queries lie in the code, the DBA cannot really optimize the queries themselves. Whether or not it was a good practice for DBA's to be doing such optimization is a very subjective thing but i have worked at companies where it worked very well.

Theres probably a lot more pros vs cons to this but these 2 throw some light on why it used to be mandated in the past.

As long as your current way of working alleviates the reason behind these points, then you can always point it out to your mgmt and ask them to relook at their standards..

Though, to be very honest, i wouldnt go with the argument of "It takes more time and effort". In my opinion, it only takes a negligible amount of effort to write Create Package .... and you could just write a small script or tool to take your query and convert it to a procedure. point here is that your argument of time / effort seem at best to be a thin one.

share|improve this answer
add comment

Well, because you'd like to keep your job is always a pretty good reason. If that's the company's/team's/management's standard, it's your job to conform to it.

The Stored Procedure vs. Client Side query argument is quite old at this point and there enough pluses and minuses for each that it's a matter of preference as it fits in your technology ecosystem.

Now, to be fair, with the spotlight on ORMs lately, auto-generated client-side queries are vindicated against some of the criticism of days past.

My own personal opinion: easier to QA and deploy sproc fixes than compiled code.

share|improve this answer
2  
The last line alone is the primary reason I always prefer proc over compiled given an option. Fixing a bug in an proc is server based and doesn't require you to recompile and redeploy the compiled code (usually). –  BBlake Oct 11 '12 at 18:42
add comment

Your Answer

 
discard

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

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