Tool for comparing result of 2 stored procs

Hi,

We are in the process of tuning the performance of our stored procs in
SQL 2000 and are looking for a tool that would help us in comparing
the result sets of an old SP and a modified SP. The tool should take
as input the names of the 2 SPs, a set of parameter values and invoke
them. Internally it should compare the result sets returned and log/
display the difference in result sets if any.
We do not want to compare response time, but want to compare result
sets just to be sure that the modifications have not changed the
functionality of the SP.
The tool should also be able to compare result sets of SPs that give
out multiple record sets.

Please let us know which tool we can use.

Thanks,
Yash

Re: Tool for comparing result of 2 stored procs

On Nov 7, 8:00 am, Yash wrote:
> Hi,
>
> We are in the process of tuning the performance of our stored procs in
> SQL 2000 and are looking for a tool that would help us in comparing
> the result sets of an old SP and a modified SP. The tool should take
> as input the names of the 2 SPs, a set of parameter values and invoke
> them. Internally it should compare the result sets returned and log/
> display the difference in result sets if any.
> We do not want to compare response time, but want to compare result
> sets just to be sure that the modifications have not changed the
> functionality of the SP.
> The tool should also be able to compare result sets of SPs that give
> out multiple record sets.
>
> Please let us know which tool we can use.
>
> Thanks,
> Yash

we developed exactly that:

http://www.simple-talk.com/sql/t-sql-programming/close-those -loopholes---testing-stored-procedures--/

Re: Tool for comparing result of 2 stored procs

Yash (yashgt@gmail.com) writes:
> We are in the process of tuning the performance of our stored procs in
> SQL 2000 and are looking for a tool that would help us in comparing
> the result sets of an old SP and a modified SP. The tool should take
> as input the names of the 2 SPs, a set of parameter values and invoke
> them. Internally it should compare the result sets returned and log/
> display the difference in result sets if any.
> We do not want to compare response time, but want to compare result
> sets just to be sure that the modifications have not changed the
> functionality of the SP.
> The tool should also be able to compare result sets of SPs that give
> out multiple record sets.

What I have done, and I don't claim to the most efficient, is to copy
and paste into two Excel sheets. The on the third Excel sheet, I add
this formula:

iif (sheet1!A1 =sheet2!A1; ""; "XXX")

and duplicated this cell all over the sheet. (Note: you have to have to
replace the semicolons with commas depending on your regional settings.)

For this to work, the result need to have an ORDER BY clause, or else
you may have accidental differences.

In my case, there is a little twist: if the result sets are completely
identical, I don't have to prepare the third sheet, or even copy into
the second. To wit, I run a tool Clipmate (from www.thornsoft.com)
that captures things put on the clipboard, and it confirms a new clip
with a sound. And if the new clip is identical to the current clip,
the sound is different!

Alas, I often that there minor differences in the result set, that are
not significant, or even are improvements of what I started with.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Tool for comparing result of 2 stored procs

On Nov 7, 7:00 am, Yash wrote:
> Hi,
>
> We are in the process of tuning the performance of our stored procs in
> SQL 2000 and are looking for a tool that would help us in comparing
> the result sets of an old SP and a modified SP.

Wouldn't it be nice if such a tool was not even necessary!

Basic Anatomy of Sql Server, part I
What is a stored procedure?
http://beyondsql.blogspot.com/2007/11/basic-anatomy-of-sql-s erver-part-i.html

Basic Anatomy of Sql Server, part II
The unit test as part of the database.
http://beyondsql.blogspot.com/2007/11/basic-anatomy-of-sql-s erver-part-ii.html

Basic Anatomy of Sql Server, part III
What does deferred name resolution really mean?
http://beyondsql.blogspot.com/2007/11/basic-anatomy-of-sql-s erver-part-iii.html

www.beyondsql.blogspot.com