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?
It’s in git. In our current but on its last legs env we had a inhouse built tool that would compare a release branch with master and build a series files we’d throw at the DBA to run updating procs, tables, views, etc. Had procs built to preserve data when ddl changed and what not. Very painful. This was on Netezza.-
Now we are moving to Azure Synapse… And we are using Azure DevOps instead of Bitbucket and it works but its jank. Our git repo is now actually tied to the DB through the use of release pipelines. PRs on master will get released automatically… That said most recent update from msft and now it takes 7hrs just for the deployment plan to build.
Its not great… My company is addicted to stored procs. I hear there is some projects looking into databricks, azure data factory, sparks, etc… For now my life is pain and complaints, just trying to get people to learn to love git. BI here before I joined unfortunately not IT minded.
Edit: didn’t even touch on testing… And frankly half the devs seem like they don’t/don’t know how to even do impact analysis… If you change a column, at minimum search the code base for impacts… Don’t just deploy and blow up our prod cause you’re mismatching sizes
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?
My companies whole of BI is operated through Stored Procs… Its a tad insane
How do you even version control or test something like this?
It’s in git. In our current but on its last legs env we had a inhouse built tool that would compare a release branch with master and build a series files we’d throw at the DBA to run updating procs, tables, views, etc. Had procs built to preserve data when ddl changed and what not. Very painful. This was on Netezza.-
Now we are moving to Azure Synapse… And we are using Azure DevOps instead of Bitbucket and it works but its jank. Our git repo is now actually tied to the DB through the use of release pipelines. PRs on master will get released automatically… That said most recent update from msft and now it takes 7hrs just for the deployment plan to build.
Its not great… My company is addicted to stored procs. I hear there is some projects looking into databricks, azure data factory, sparks, etc… For now my life is pain and complaints, just trying to get people to learn to love git. BI here before I joined unfortunately not IT minded.
Edit: didn’t even touch on testing… And frankly half the devs seem like they don’t/don’t know how to even do impact analysis… If you change a column, at minimum search the code base for impacts… Don’t just deploy and blow up our prod cause you’re mismatching sizes