How do you compare the results of two stored procedures in SQL Server?

 Comparing the results of two stored procedures in SQL Server can be done in several ways, depending on the specifics of the data and the comparison criteria. Below are some common methods you can use to compare the results of two stored procedures:

1. Use a #Temp or @TableVariable to Store Results

One of the simplest methods is to store the result sets of both stored procedures in temporary tables or table variables, and then compare them using a JOIN, EXCEPT, or INTERSECT operation.

Example with Temporary Tables:

-- Create temporary tables to hold the results of each stored procedure
CREATE TABLE #Result1 (Column1 INT, Column2 NVARCHAR(100));  -- Adjust columns as needed
CREATE TABLE #Result2 (Column1 INT, Column2 NVARCHAR(100));  -- Adjust columns as needed

-- Insert results from the first stored procedure
INSERT INTO #Result1
EXEC YourFirstStoredProcedure;

-- Insert results from the second stored procedure
INSERT INTO #Result2
EXEC YourSecondStoredProcedure;

-- Compare the results
-- Example 1: Find rows that are in #Result1 but not in #Result2
SELECT * FROM #Result1
EXCEPT
SELECT * FROM #Result2;

-- Example 2: Find rows that are in #Result2 but not in #Result1
SELECT * FROM #Result2
EXCEPT
SELECT * FROM #Result1;

-- Example 3: Find rows that are common to both
SELECT * FROM #Result1
INTERSECT
SELECT * FROM #Result2;

-- Drop temporary tables after use
DROP TABLE #Result1;
DROP TABLE #Result2;

• EXCEPT: Returns rows from the first result set that are not in the second result set.
• INTERSECT: Returns rows that are common to both result sets.

Example with Table Variables:

DECLARE @Result1 TABLE (Column1 INT, Column2 NVARCHAR(100));  -- Adjust columns as needed
DECLARE @Result2 TABLE (Column1 INT, Column2 NVARCHAR(100));  -- Adjust columns as needed

-- Insert results from the first stored procedure
INSERT INTO @Result1
EXEC YourFirstStoredProcedure;

-- Insert results from the second stored procedure
INSERT INTO @Result2
EXEC YourSecondStoredProcedure;

-- Compare results using EXCEPT or INTERSECT as described above
SELECT * FROM @Result1
EXCEPT
SELECT * FROM @Result2;

SELECT * FROM @Result2
EXCEPT
SELECT * FROM @Result1;

SELECT * FROM @Result1
INTERSECT
SELECT * FROM @Result2;

2. Use FULL OUTER JOIN to Compare Row-by-Row

If you want a more detailed comparison, you can use a FULL OUTER JOIN to find the differences between two result sets. This will show rows that exist in either one of the result sets but not in the other.

-- Compare results using FULL OUTER JOIN
SELECT
    ISNULL(r1.Column1, r2.Column1) AS Column1,
    ISNULL(r1.Column2, r2.Column2) AS Column2,
    CASE 
        WHEN r1.Column1 IS NULL THEN 'In Result2 Only'
        WHEN r2.Column1 IS NULL THEN 'In Result1 Only'
        ELSE 'Match'
    END AS Comparison
FROM 
    (EXEC YourFirstStoredProcedure) AS r1
FULL OUTER JOIN 
    (EXEC YourSecondStoredProcedure) AS r2
    ON r1.Column1 = r2.Column1 AND r1.Column2 = r2.Column2;

3. Row Count Comparison

If you're only interested in the number of rows returned by the two stored procedures, you can compare the counts directly.

DECLARE @Count1 INT, @Count2 INT;

-- Get row counts
EXEC @Count1 = YourFirstStoredProcedure;
EXEC @Count2 = YourSecondStoredProcedure;

-- Compare the counts
IF @Count1 = @Count2
    PRINT 'Row counts are the same';
ELSE
    PRINT 'Row counts are different';

4. Check Execution Plans (For Performance Comparison)

If you're looking to compare performance, SQL Server’s execution plan can help. You can analyze the execution plan of each stored procedure to see if they are optimized differently, although this doesn't compare the results directly.

-- Show execution plan for the first stored procedure
SET SHOWPLAN_ALL ON;
EXEC YourFirstStoredProcedure;
SET SHOWPLAN_ALL OFF;

-- Show execution plan for the second stored procedure
SET SHOWPLAN_ALL ON;
EXEC YourSecondStoredProcedure;
SET SHOWPLAN_ALL OFF;

5. Automate Comparison with a Script

For repeated comparisons, you can automate this process with a script that stores the results, compares them, and even generates a report on the differences.

Example of Automated Comparison Script:

DECLARE @Result1 TABLE (Column1 INT, Column2 NVARCHAR(100));
DECLARE @Result2 TABLE (Column1 INT, Column2 NVARCHAR(100));

-- Insert results from the stored procedures
INSERT INTO @Result1
EXEC YourFirstStoredProcedure;

INSERT INTO @Result2
EXEC YourSecondStoredProcedure;

-- Check if the results are identical
IF NOT EXISTS (
    SELECT * FROM @Result1
    EXCEPT
    SELECT * FROM @Result2
)
    PRINT 'Results are identical';
ELSE
    PRINT 'Results are different';

-- Optional: View the differences
SELECT * FROM @Result1
EXCEPT
SELECT * FROM @Result2;

SELECT * FROM @Result2
EXCEPT
SELECT * FROM @Result1;

This method can be set up to run on a schedule, and the results can be logged to a table or sent via email.

6. Use SQL Server Data Tools (SSDT) for Comparison

For more complex scenarios, you might consider using SQL Server Data Tools (SSDT) or third-party tools (like Redgate SQL Compare) to automate and manage database comparisons, especially when comparing data structures, not just the results.

Conclusion

• EXCEPT and INTERSECT are excellent ways to compare result sets directly.
• Temporary tables or table variables are useful for storing the result sets for further comparison.
• For performance comparisons, look into execution plans.
• You can automate comparisons with scripts for consistent results.