20 years doing this, including tooling that parses stored procs out of systems nobody fully understands anymore, and I don't think sprocs are the problem people think they are. Where they go wrong is time, not technology.
A well-versioned, well-owned sproc estate today can still be unreadable in 10-15 years, not because the language is bad, but because nothing tracks the drift: the author leaves, the ticket explaining a weird CASE branch gets closed and forgotten, someone patches around a bug three teams away, and now you've got T-SQL nobody currently employed fully understands, with git blame pointing at people who don't work there and comments referencing tickets that don't resolve. That's not a stored-procedure problem, it's an "institutional knowledge decays and nothing makes that visible" problem. It happens to application code too, just slower, since app code at least throws stack traces.
The thing that makes sprocs worse: they stay syntactically correct and keep answering queries even after the business meaning underneath drifts, because there's no compiler error for "this no longer matches reality." I regularly find procs whose own header comment describes something the code three lines below no longer does. Nothing broke. It just quietly returned a slightly wrong number until someone looked.
twellborn · · focus · HN ↗
A well-versioned, well-owned sproc estate today can still be unreadable in 10-15 years, not because the language is bad, but because nothing tracks the drift: the author leaves, the ticket explaining a weird CASE branch gets closed and forgotten, someone patches around a bug three teams away, and now you've got T-SQL nobody currently employed fully understands, with git blame pointing at people who don't work there and comments referencing tickets that don't resolve. That's not a stored-procedure problem, it's an "institutional knowledge decays and nothing makes that visible" problem. It happens to application code too, just slower, since app code at least throws stack traces.
The thing that makes sprocs worse: they stay syntactically correct and keep answering queries even after the business meaning underneath drifts, because there's no compiler error for "this no longer matches reality." I regularly find procs whose own header comment describes something the code three lines below no longer does. Nothing broke. It just quietly returned a slightly wrong number until someone looked.