0

I am currently in my first couple of months of developing on MSSQL and currently writing some stored procedures for importing data from temporary tables. Writing stored procedures from start to finish is not something I have much experience in.

Looking at the previous developers work I can see he creates one main stored procedure and it then calls multiple stored procs within to complete some inserts and then move onto the next section.

What are the benefits of using one proc which calls more stored procs over just one large stored proc with multiple inserts one after each other into separate tables? I can't seem to find a list of benefits for this. I am not opposed to it just want to use "best practice". These stored procs will not be re used other than this one use case.

1 Answer 1

1

What are the benefits of using one proc which calls more stored procs over just one large stored proc with multiple inserts...

Readability, Maintainability, Re-usability, Testability: Basically, all the same benefits as one would expect in an object-oriented programming language that's structured properly when following single purpose principle.

By breaking up the code in a reasonable way, each procedure becomes a smaller set of code that's more readable and easier to maintain specific changes on. It also allows you to re-use those smaller stored procedures multiple times (refactoring the code) within the same main calling procedure or in other consumers. And finally, smaller blocks of code, separated into their own respectable procedures, makes testing easier and more efficient.

Aside from that, it also makes debugging, error handling, and logging easier when trying to narrow down what part of the code is failing (as mentioned by Erik).


All of that being said, not all application code design principles necessarily apply or apply as well to database design. Improper or over-refactoring can result in other issues such as with performance sometimes. So always be mindful and test accordingly.

1
  • 1
    It’s also useful for error logging/handling to know when exactly which step in the process failed. Apr 5, 2023 at 23:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.