How to Compare Stored Procedure Result Sets
-
Posted on December 9, 2009 by Derek Dieter
-
0
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:
- What is in one result set and not in the other
- All results that do not match
- All results that do match
First, let’s dump the procedures into tables:
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')
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.
SELECT *
FROM #Procedure1
EXCEPT
-- That does not match procedure2
SELECT *
FROM #Procedure2
Next, we will get what is in procedure2 and not in procedure1.
SELECT *
FROM #Procedure2
EXCEPT
-- That does not match procedure1
SELECT *
FROM #Procedure1
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.
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
Lastly, let’s get what does match in both procedures. We can do this using either INTERSECT (2005+) or CHECKSUM.
SELECT *
FROM #Procedure1
INTERSECT
SELECT *
FROM #Procedure2 ds
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
- Comments (RSS)
- Trackback
- Permalink
The last line of TSQL above when looking for result sets matches
AND ds.ChkSum ps.ChkSum
should be
AND ds.ChkSum = ps.ChkSum
hi
how do i pass date value in openquery?
how do i give two values in single parameter?
for Example:
single value
SELECT *
INTO #Procedure1
FROM OPENQUERY(LOCALSERVER, ‘set fmtonly off exec dbo.Procedure1 1,hr’)
more than on value
SELECT *
INTO #Procedure1
FROM OPENQUERY(LOCALSERVER, ‘set fmtonly off exec dbo.Procedure1 ’1,24′,hr’)
here single quotes not allowed in parameters
