SQL Stored Procedures
SQL Stored Procedures
This SQL tutorial will answer to the questions like what is a stored procedure and what are the Advantages of using stored procedures.
This explains SQL stored procedures and their advantages.
A stored procedure is like making an API for your DB schema. It made a fair bit of sense before microservices existed.
Nowadays you really shouldn’t be exposing the DB schema in the first place.
This my hot take: Do not use stored procedures with applications. Keep your data separate from your code.
Could you explain more? Almost everywhere I've worked from Fortune 250 on down has used stored procedures with applications and it seems extremely clean and performance-oriented.
If anything, it separates code from the data more as far as I can tell, so maybe I'm missing something?
Also, if something is somewhat data driven and there's a bug, you simply alter a procedure versus doing a build and deploy of the entire application.
If anything, it separates code from the data more as far as I can tell, so maybe I’m missing something?
Stored procedures are code -- so you're putting code in the database. How do you test that code? How do you source control that code? How do you roll back that code to the previous version or compare it to a previous version? How to know the history of that code? If that procedure is designed to work in together with application changes, how to test and deploy those together? This is all not impossible but it's certainly more difficult and creates more potential failure points.
Also, if something is somewhat data driven and there’s a bug, you simply alter a procedure versus doing a build and deploy of the entire application.
That's the problem. You write like that like it's an advantage but you're literally editing code live in production.
The performance advantages of stored procedures are unsupported. Most database engines do not treat stored procedures any differently than regular queries. And it's not that stored procedures aren't optimized, it's that queries are equally optimized.
Fortune 250 on down has used stored procedures with applications and it seems extremely clean and performance-oriented.
A lot of these companies also still use COBOL on mainframes (something I've actually worked on and don't recommend either). Stored procedures made a lot more sense historically when SQL might actually have more expressive power than your programming language and when database interfaces were much complicated and non-standard.
In my experience, 9/10 times a stored procedure is code that should just be SQL executed from your application (as in, your application sends the query text to the db, rather than that text being stored in the database and executed via calling a stored procedure).
If it’s necessary for performance, sure, go for it. Satisfy your requirements. If it’s not necessary for performance, ask yourself why it needs to be a stored procedure deployed to the database.
My boss is a DB dev and requires that all our database transactions go through stored procedures. Oh, and those stored procedures contain all our business logic.
My condolences. This is the stuff Robert C. Martin talks about in his book “Clean Architecture”; database vendors locking you into their tech.
Your boss isn’t just a developer, he’s a *db developer *. From his perspective, the database is a god rather than a means to store information.
I should add onto my original post that the queries should strive to be as database agnostic as possible. This alleviates a lot of pain when the company decides to move from one DBMS to another.
Is it safe to assume your stored procedures have lots of DBMS specific functions and syntax sprinkled among the code?