Best Practices regarding Stored Procedures.

General Discussion
1

Hello LB Community!
    I am once again working on the LB integration with a multi-module Maven2 project and am hoping to get some feedback on some best practices in regards to managing Stored Procedures with LB on SQLServer…  That being said, here’s some background: We currently have 50+ SPs in our system that get modified from time to time and I’m wondering what the best way to approach versioning these would be.  Since we are implementing LB on a legacy DB and will not have the need to install a full db, we have decided it would be best to use ALTER statements in all of our SP sql files.  I have toyed with the idea of using a databaseChangeLog that contains changeSets with the runOnChange attribute set to true that includes a sqlFile element pointing to each of our SP sql files.  This way, when a dev makes a change to the SP, they needn’t worry about creating a new changeset for the modification.  LB will pick up on the fact that it has been changed and will run the changeset.  A dev would create a new changeSet element when a new SP is created.  I guess the biggest question I have in regards to going this route is how to handle rollbacks?  I’m aware of the rollback element but would hate to require that a dev populate this element with the version of the SP sql file that existed before modification (IMO, this would be too awkward for devs to keep up with)…  What do others do to solve this seemingly common problem?

Any and all feedback is greatly appreciated in advance.

Thanks!
-Ryan

2

The way you are describing is (modifying the SP and using the runOnChange attribute) how I generally use them.  The biggest advantage I see is that you can do diffs easily between revisions and see exactly what changed in your SPs. 

Like you said, however, rollback is more difficult.  My solution to that is generally to just handle SP rollbacks in a manual manner based on older source code revisions.  During development you don’t really need to roll back using liquibase, you just roll back your source and do an update and liquibase sees that it changed and runs your alter statement.  Rolling back production would require running the SP alter statements manually from old revisions, but for me that is an infrequent enough thing, that the cost of that is outweighed by the developer efficiency of not having to deal with it.

Nathan

3

Thanks (as always), Nathan.  Glad we got this one right  ;D