I second that. SPs/funcs have this weird tendency to always stay hidden in the fringes and out of sight, easily forgotten when adding new functionality, easily overlooked when making changes elsewhere.
> If you don't have the discipline to do these things then they are likely best avoided.
I'd go further and say you should avoid databases and maybe even persistence entirely if you don't have the discipline to do the above. Sprocs will be the least of your problems otherwise.
Before the development of decent migration systems it was incredibly common for database structure - including stored procedures - to be treated independently of source code in a repository.
True, of course. There were also undoubtedly a lot of production systems that didn’t even use version control for non-database code. Industry practices certainly evolve over time. But it’s difficult to imagine a scenario where a team is aware of version control, uses it for the things they realize are code, but somehow doesn’t realize that stored procedures are code.
It's a common thing to miss. There's a reason SQL injections are (unless things have changed recently) among the most prevalent classes of web exploits.
Yeah, I think so. But my hunch is that the majority of people who tell you never to use stored procedures have been burned by these techniques not being used for them.
From my experience only if there are dedicated DBAs and you have too many systems running - then you forget one. If you only have server code and the stored procedures in the same repository, with migrations, this problem goes away.
> SPs/funcs have this weird tendency to always stay hidden in the fringes and out of sight, easily forgotten when adding new functionality, easily overlooked when making changes elsewhere.
This is the classic "carpenter blames his tools for crappy results" argument. Implementation isn't easy.
If the developer doesn't know / doesn't document the project has code embedded in the database, that's on the developer, not the tools. Because the use of any developer tools requires a certain level of competence in order to use them successfully.
- they live in source control
- they are covered by automated tests
- they are applied using some form of automatic database migration system (not by someone manually executing SQL against a database somewhere)
If you don't have the discipline to do these things then they are likely best avoided.