How do you maintain large t-sql procedures

Question

I'm about to inherit a set of large and complex set of stored procedures that do monthly processing on very large sets of data.

We are in the process of debugging them so they match the original process which was written in VB6. The reason they decided to re write them in t-sql is because the vb process takes days and this new process takes hours.

All this is fine, but how can I make these now massive chunks of t-sql code(1.5k+ lines) even remotely readable / maintainable.

Any experience making t-sql not much of head ache is very welcome.

Answer 1

First, create a directory full of .sql files and maintain them there. Add this set of .sql files to a revision control system. SVN works well. Have a tool that loads these into your database, overwriting any existing ones.

Have a testing database, and baseline reports showing what the output of the monthly processing should look like. Your tests should also be in the form of .sql files under version control.

You can now refactor your procs as much as you like, and run your tests afterward to confirm correct function.