5 ms·
I remember people I used to work with arguing against stored procedures for two main reasons. 1) Version Control - I guess a lot of the stored procedures were
by LouisSayers 6y ago
I remember people I used to work with arguing against stored procedures for two main reasons.
1) Version Control - I guess a lot of the stored procedures were being put straight into the DB without recording a history of the changes. These days you could easily do this using DB migrations I guess.
2) Testing - is unit testing a thing for Stored Procedures? I guess again, you might be able to do this from code as well programatically adding a stored procedure, running a bunch of tests and removing it again.
I do wonder - what do people generally do in practice for overcoming these objections?
Does anyone have any other objections around using stored procedures?
- Ma8ee 6y agoI'm using Red-gate tools for all of the above. They are covering source control and unit testing and a lot more, but only for SQL Server. My previous employer used some homespun systems for the same. A bit clunky, but easier to do than most people thinks.
- breischl 6y ago1) I find this kinda funny. Why are you worried about this in SQL but not for other code? It's not like it's hard to chuck Python, JS, Ruby, ASP, etc code straight into prod, you just don't do that because it's stupid. Don't do it for SQL either. If you really want to, build out user permissions that only allow your CICD system to change them. 2) TBH we never built anything complex enough to need this, and I would tend to think that if you do need this you're probably overcomplicating your DB. But you could probably do something that creates a temp DB, populates example data, and then runs tests.
- pjmlp 6y agoBoth Oracle and SQL Server do support versioning tools and unit tests for their stored procedures. You also have graphical debuggers for them.