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 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 2

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