stored procedure review

[washaw]

Hi all,

we are planning to upgrade our database to 2005, and one of the process is to review all stored procedures wriiten in 2000 and change'em in such a way that they should work fine in 2005.
my question is what should I do when reviewing the procedures

Thanks

 

[SQLSister]

Most of your procs should work, I can't really think of anything that isn't supported that you could do in SQL Server 2000. What you want is to find the ones that would benefit from some of the new t-sql code available.(look for a list of the new functionality to help you decide what to look for.) For instance pivot tables are far simpler (And probably more efficient) in SQL server 2005.

To do this systematically, I would set up a test database. Then run each proc once to make sure it works. Have a table or spreadsheet with each procs name and record the day you did the test and the results, so that you can prove you did test everything. Add a column for changes, so that if you change any proc you will know what you changed. You might even consider a column to identify which procs could use optimizing but which currently work, then go through and fix those after you know everything works.

Not only procs but used defined functions and triggers and views will need a review to be complete.

While you are at it fix some stuff that you probably have that will run but should be better. Replace any @@identity calls with scope_identity. Look at any cursors to see if they can be replaced with set based logic. Get rid of any old style joins (you know, the ones using the commas). Rewrite any triggers that assume single record processing. If you should have the transaction isolation level set in the proc but some of the old ones do not have it, then fix those sort of issues at the time of the review.

You might also take this opportunity to review the code of your user interfaces to see if you have stored procs that are no longer being called. Those you can get rid of after reveiwing to make sure they aren't quarterly reports sent in a dts package or job or used in data imports or some such.

Have fun.


"NOTHING is more important in a database than integrity." ESquared

 

[washaw]

Thank you SQLSister, I really appreciate that