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.
3
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.
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.
2