How to Compare Stored Procedure Result Sets

Comparing the results of two different stored procedures output can be difficult task. The first difficulty is to return the result sets of the stored procedures into temporary tables. There are two different methods of doing this outlined in the link above. For this example we will use the local linked server method. Your method may differ depending upon whether your system setup will support one of the methods. It may also differ if you are looking at passing dynamic parameters (this method is also covered in the link above).

After the result sets are in the temp tables, we need to compare the datasets. We typically need to see the data in one of three ways:

1. What is in one result set and not in the other
2. All results that do not match
3. All results that do match

First, let’s dump the procedures into tables:

[cc lang=”sql”]
— Where local server is the name we gave to our local linked server
SELECT *
INTO #Procedure1
FROM OPENQUERY(LOCALSERVER, ‘set fmtonly off exec dbo.Procedure1 param’)

SELECT *
INTO #Procedure2
FROM OPENQUERY(LOCALSERVER, ‘set fmtonly off exec dbo.Procedure2 param’)
[/cc]

Now, considering we have SQL 2005 and above, we will compare what is in Procedure1 that does not match what is in procedure2. We do this using the new EXCEPT Statement.

[cc lang=”sql”]
— Give us what IS in procedure1
SELECT *
FROM #Procedure1

EXCEPT
— That does not match procedure2
SELECT *
FROM #Procedure2
[/cc]

Next, we will get what is in procedure2 and not in procedure1.

[cc lang=”sql”]
— Give us what IS in procedure2
SELECT *
FROM #Procedure2

EXCEPT
— That does not match procedure1
SELECT *
FROM #Procedure1
[/cc]

The next result set comparison is to get all results from both procedures that do not match. We do this using the CHECKSUM function. We do not need SQL 2005+ for this.

[cc lang=”sql”]
SELECT *
FROM (
SELECT ID
,ChkSum = CHECKSUM(*)
FROM #Procedure1 ds
) ds
JOIN(
SELECT ID
,ChkSum = CHECKSUM(*)
FROM #Procedure2 ps
) ps
ON ds.ID = ps.ID
AND ds.ChkSum != ps.ChkSum
[/cc]

Lastly, let’s get what does match in both procedures. We can do this using either INTERSECT (2005+) or CHECKSUM.

[cc lang=”sql”]
— Using intersect, we don’t need to worry about the order of the table selects
SELECT *
FROM #Procedure1
INTERSECT
SELECT *
FROM #Procedure2 ds
[/cc]

[cc lang=”sql”]
SELECT *
FROM (
SELECT ID
,ChkSum = CHECKSUM(*)
FROM #Procedure1 ds
) ds
JOIN(
SELECT ID
,ChkSum = CHECKSUM(*)
FROM #Procedure2 ps
) ps
ON ds.ID = ps.ID
AND ds.ChkSum != ps.ChkSum
[/cc]