DEV Community


Discussion on: When is it a good idea to use stored procedures?

cess11 profile image

When I used the IIS/ASP/MS SQL Server stack on a daily basis I found it very convenient over time to massage anything DB related that should stick around in the code base into smaller fractions as stored procedures.

This improved overall performance and helped keep data and input validation reliable.

It can however cause some trouble when one needs to restructure the project or part of it, since it is common for these procedures to be less than general unless one actually took the time to refactor and break them down into small pieces at once when saving them.

So as a rule of thumb in big web applications I would recommend to do whatever works for fast development in testing but take the time needed to get from dynamic, interpreted and flexible to really tight and precise stored procedures once it is moved towards production.

In other types of applications it gets more muddled and depends more on the particular work the program needs to do and in what environment. Not all programs run in such hostile and weird environments as the open web, and some applications have no good use for stored procedures but still make heavy use of databases and information stores, perhaps backend stuff like REDIS as a link between automated and greedy data collection and something like a logic program KB, or more obscure systems like where DB, code and data are basically the same thing and integrates tightly with web GUI and an inference engine.