quality management on SQL stored procedures

Question

I've always developed my own applications and use certain criteria to evaluate if the code is acceptable from a quality perspective. Now I've switched jobs and have to among other things maintain existing software.

Here they put most of their business logic in stored procedures. This in itself I see as a bad practice, but it's the hand I'm dealt with regardless. Now I've seen them using stored procedures that contain more then 2k LoC excluding comments and if I would apply that to a method in Java/C#/C I would give it a big fat minus minus rating.

But then again should I see a stored procedure as an method for using quality metrics? You could argue for example that stored procedures uses more LoC to realize something and you should take that into account. But then again if the same method consists of 20 lines of code in Java/C# and in a stored procedure it consists of 50 lines I feel like I should take it into account as it decreases the readability of the procedure.

Should I use the metrics as described in https://www.researchgate.net/publication/4276839_A_Practical_Model_for_Measuring_Maintainability to see if the code is acceptable from a maintainability perspective? These are: LoC, Cyclomatic complexity, Unit Size, Duplication, Testability (where unit size refers to a method and LoC to total lines of code for a class).

Or should I use different quality metrics to see if the stored procedures are acceptable from a quality perspective? testability can be scrapped in any case as there are no tests as far as I'm aware of here.

Answer 1

I would say there is little comparability between the SQL environment and that of standard programming languages in the C tradition.

A stored procedure of 2k lines is certainly a handsome size, but the appropriate comparison for the amount of data processing work it does is probably not a single class, but a single small application.

SQL is a very old language and to some degree it lacks the modern facilities for code organisation that we now take for granted in standard languages.

It is however relatively tuned for the problems that arise in a database environment. One of the benefits you get from having everything in one procedure, for example, is the ability to update the procedure atomically without even taking the database offline. Contrast that with updating desktop applications on a myriad of client machines, as may once have been the main alternative.

It is also a relatively straightforward way of doing the work of a small application, without having to leave the database environment. And it's all backed up with the database and transactionally consistent.

Breaking things down into multiple subroutines, at a granularity typical of a standard programming language, would often create quite a mess within the limitations of what SQL has to organise such subroutines and control access to them.

So no, I wouldn't attempt draw any analogy from metrics that are tailored to standard programming languages.

Of course I can't speak for whether using stored procedures is appropriate to the work being done in this instance. More complicated processing has always been handed off to independent applications written in a different language than SQL.

And the stored procedure should be well-structured as much as possible, with clear commentary and sequential progression through steps that transform data. But good structure here will be judged by different standards and compromises than code in a standard language, and any research that applies to it.