Version changes for Stored Procedures

Question

I have an application that relies very heavily on stored procedures (SQL 2005/2008). We are doing a minor update that will modify 25-35 of these stored procedures. The application is such that both versions of the stored procedure must be available.

This is major version 4 of the application and usually we've been able to completely modify the data structure to go with each new version. However in this case, we cannot do that.

Here are my 2 options I've come up with

1. Make a "2" version of each stored procedure. If I had a procedure called getUser create a getUser2. The downside of this is that the # of stored procedures will grow exponentially with each version change

2. Add a @version parameter to each stored procedure that defaults to v1. This would keep the number of stored procedures down but would bloat each stored procedure

Anyone have any thoughts on this? Any other clever ideas?

Cody

Answer 1

I wouldn't create two different files that's for sure.

Maybe in your source control you should make a branch of all of your version ones, and then a new branch with your next version, then you can include both branches as separate folders on your system and have your business logic point to the right location.

This solution may be a little sloppy, but I think it is the lesser of several evils.

Regardless, actually versioning your stored procedure code is a definite must in my opinion.

Answer 2

I would suggest the second option you provided. Use a version parameter. This is what web services do so they don't break the code of apps that started using the API a long time ago but the API is updated at some point.

I bet there is some logic that is the same between the two versions that you could abstract out into the bottom of the proc or something. Or potentially create functions for common elements and call those functions within your big IF/SWTICH blocks.

Answer 3

I would take this opportunity to make a move from stored procedures to an ORM or some other approach. Both of your proposed solutions would require some sort of code change to decide which stored procedure to use. Instead I'd have it decide whether to use the stored procedures or the ORM. I would also make up plans to phase out most of the stored procedures.

I've seen a lot of bad code and messed up systems in my career but nothing dashes my hopes that a project can be salvaged like seeing GetItemFromLots_2_Temp_2 in the stored procedures list. Multiple methods are way prettier and way easier to maintain than multiple stored procedures.

(To others who love stored procedures. I'm not saying they're bad. I'm sure there are clever approaches to handling this sort of thing using stored procedures but, if such an approach were being used, this question wouldn't have been asked.)

Answer 4

Modify the existing stored procedures so that the new logic is executed conditionally, only when the proc is called under those circumatances where the new logic should get executed... If the new proc would have a slightly different interface (set of sProc parameters) then you could make them optional and use the presence or absence of the parameter a switch to control which chunkl of code gets executed within the proc...

When the old logic is no longer needed you can simply remove it from the sProcs

Answer 5

I would go for the two files option for 2 reasons:

• The signature, that is the number of parameters can change btween versions
• Each stored procedure would be simpler, therefore less chance of errors from all conditional code

Answer 6

We used to use stored procedures extensively at my company, but have (mostly) moved away from them towards ORM of late.

We do still use them, and our versioning is the same as it was before: Each time we modify the stored procedures that remain (which only a few people have rights to do), we save the SQL off, and store the .sql file in our version control.

It's imperfect and we lose a lot of the integration we have between source control and our code files (as there's no SQL server integration into TFS) but it's better than no source control at all.

EDIT - and, of course, this only works if you no longer need to use the old version of the stored proc, as it will no longer exist in a runnable form.

Answer 7

Another interesting approach would be to store all of the code for your stored procedures in a database table, along with the version for the code. Then you have a "front end" proc that handles requests and then, depending on version, will dynamically create a proc and execute it. It can then drop the proc when done.

Just an off the wall suggestion but may work out for you.