3 ms·

At work we almost exclusively use pure stored procedures[1] and everything is normalized very well. It is an absolute joy to write SQL, because of how terse it

At work we almost exclusively use pure stored procedures[1] and everything is normalized very well. It is an absolute joy to write SQL, because of how terse it is while still being very readable. Trying to implement business rules about data relations outside of the DB is a nightmare. [1] We use dynamic SQL within stored procedures for pivots.

sozy777 6y ago
You lose source control on your procedures. How you deal with that?
- dragonwriter 6y ago
  > You lose source control on your procedures Why would you do that?
- Akronymus 6y ago
  We use daily backups of the whole server, so if we really need to rollback, we can. And if we absolutely need source control, we could write a procedure in combination with a trigger to automatically write the stored procedure to some source control. Also there are tools out there that provide that functionality. Found with a few seconds of searching. https://host.apexsql.com/sql-tools-source-control.aspx https://host.apexsql.com/sql-tools-source-control.aspx
- piva00 6y ago
  Why? When I worked doing infrastructure and CI/CD automation we created a pipeline for deploying stored procedures, not that different from any other code lifecycle process.
- diegoperini 6y ago
  If you use a DBMS to manually update these, yes. Otherwise you can do this in code whenever you want in your build/deploy pipeline.
- djrobstep 6y ago
  Just wrote a stored procedure in a text file and committed it to git. Seemed to work ok.
- gregw2 6y ago
  You can use Liquibase or Flyway and an automated deployment process to keep your SQL code in sync with non-SQL code (if needed.) For bonus points, you can make your stored procedures be callable by other stored procedures, create/teardown mock data, and do TDD where your test suite of stored procedure tests runs on build during deployment and either has a PASS and deploys or hits a FAIL and the deployment aborts.

sozy777 6y ago

You lose source control on your procedures. How you deal with that?

dragonwriter 6y ago
> You lose source control on your procedures Why would you do that?
Akronymus 6y ago
We use daily backups of the whole server, so if we really need to rollback, we can. And if we absolutely need source control, we could write a procedure in combination with a trigger to automatically write the stored procedure to some source control. Also there are tools out there that provide that functionality. Found with a few seconds of searching. https://host.apexsql.com/sql-tools-source-control.aspx https://host.apexsql.com/sql-tools-source-control.aspx
piva00 6y ago
Why? When I worked doing infrastructure and CI/CD automation we created a pipeline for deploying stored procedures, not that different from any other code lifecycle process.
diegoperini 6y ago
If you use a DBMS to manually update these, yes. Otherwise you can do this in code whenever you want in your build/deploy pipeline.
djrobstep 6y ago
Just wrote a stored procedure in a text file and committed it to git. Seemed to work ok.
gregw2 6y ago
You can use Liquibase or Flyway and an automated deployment process to keep your SQL code in sync with non-SQL code (if needed.) For bonus points, you can make your stored procedures be callable by other stored procedures, create/teardown mock data, and do TDD where your test suite of stored procedure tests runs on build during deployment and either has a PASS and deploys or hits a FAIL and the deployment aborts.