I'm currently rewriting some fairly old and complex stored procedures originally written in SQL2000 that I believe can be simplified using SQL2005 constructs.

I want to be sure that I do not miss any subtleties in the original procedures and inadvertantly change the behaviour of them when particular parameters are passed. (e.g by dismissing some part as redundant only to find out later that it wasn't!)

It occurred to me that I could log all calls to the stored procedures for, say, a week or so and then run these stored procedure parameters against both old and new versions and verify that there are no discrepancies. (or just auto generate a wide selection of parameter values myself and run the same test)

Does anyone know of any software that will assist in this?

Edit: I've tried save as text and then comparing with winmerge but it still  takes a bit of manual faffing around with the files to get them comparable so the only differences are differences in results rather than differences in number of dashes it appends to the file for column headers etc.

Have you tried using a SQL Profiler Trace to log the Stored Procedure calls ?

....Oops, from Frank's reply I have misundersood the question. Post Edited.

Sorry to ask, but can't you simply deploy them to your test environment first, have them checked there and then dpleoy them to production once you have some sign-off that they work as expected?

If I worked in a company with that sort of setup that would doubtless be a good solution!

In the absence of that I suppose what I'm doing is effectively building a fairly exhaustive set of unit tests that I can run against both versions myself.

Results to Text + Winmerge get part of the way but It would be nice if there was a generic tool that could take multiple pairs of queries loop through them and report discrepancies in a user friendly way.

There is a tool called SQL Refactor,  which is an add-in for Management Studio in SQL 2005.
you can download demo version from:
hxxp://www.red-gate.com/products/SQL_Refactor/index.htm

Some features are:
    * Layout SQL, which formats  SQL statements.
    * Uppercase Keywords
    * Find unused variables and parameters
    * Qualify object names
    * Expand wildcards
    * Encapsulate selection as a new stored procedure
    * Smart rename a database object
    * Table split
    * Script summary

MartinSmithh:

If I worked in a company with that sort of setup that would doubtless be a good solution!

In the absence of that I suppose what I'm doing is effectively building a fairly exhaustive set of unit tests that I can run against both versions myself.

Results to Text + Winmerge get part of the way but It would be nice if there was a generic tool that could take multiple pairs of queries loop through them and report discrepancies in a user friendly way.

Hm, sorry to hear that. Good luck!