Managing Stored Procedures / Functions for releases.

WE release our software once a week. In 1 months time we will have over 500 stored procedures in our DataBase.

What we do for releases is when a stored procedure is changed, we put the Drop and Create parts of that script in our SQL Update Script.

A problem comes up when Developer A changes My_StoredProc and then developer B changes the same stored procedure. Sometimes it works ok (the developer B will run the update script before changing his stored procedure. HOwever, it can happen where one Update script file has the same SP 5 times (5 drops 5 creates)... especially if over 300 SP's are getting updating in 1 release.

We will always catch these on our tests, however, it's the 2 hours to fix the Test DB after we run these tests...

What is the best way to manage these? We thought about putting our stored procedures into Team Foundation Server, but we don't know if that will work for us.

We have 8 developers in our team.

If anyone could help or give advice on this, it would be awesome.

Thanks.

You need some form of source control system. If you have Visual Source Safe then put the DROP/CREATE scrips in it and before any developer changes an SP they make sure it isn't checked out. They check it out, do some work, then check the new update script in.

If you are all using different databases the developer should run the script that is in Source Safe against their database before making changes to ensure they are working on the latest version.

|||

Thanks, That is currently what we're switching to.

I wanted to know if there's anything better we should be doing :D

now we need to write a file to throw them all together.