Google+ Followers

woensdag 5 maart 2014

Stored procedures, yes or no?

Today I stumbled onto a stack-exchange post by a guy who was very, very clear about how extremely bad practice it is to use stored functions. His argument (in it's entirety) was that his 30+ years of experience tought him that they are a maintenance nightmare.

So, is that true? No, not really, but I understand what he's on about.

When you work on scripts or source code you can update each file separately and in the case of PHP or Python you can even update separate files directly on a production server. A stored procedure can depend on other database entities, some of which may even have to be dropped before the update can proceed.

The thing is; you can't really just update separate files on a production server, for bovious reasons, I would think. A proper update should always bring the system into a single-user mode, then do the update, test the new code, and then bring the server back into mutli-user mode.

If the system is going into single-user mode anyway, it nolonger matters how many changes you make, you can easily execute a single SQL file that removes and re-creates a set of dependent SP's. It is exactly the same operation you would do if you had to update several interdependent sourcefiles.

So no, it's not a nightmare, it's a simple matter of getting your procedures in order.