A Tool for Comparing Result Sets From Stored Procedures Across Multiple Databases - SQL Server 2008

We are updating numerous stored procedures that touch nearly every transaction in our database. What I'm wondering is if there is a tool out there where we could have it run the two stored procedures on multiple databases and compare the results sets, not only on if the data is the same, but also compare if the returning data has the same attributes (field returns 1 and it is an Integer for one stored procedure, but it is a Bit in the other).

Currently, I am logging into a remote database, running the stored procedures and pasting the results in Excel. Then, I run a Macro to see if the data matches.

I'd like to find a tool that I could set u开发者_StackOverflow社区p to log in, run the stored procedures and do the compare without my doing it manually.

You can do this manually a couple of ways by using some of the built-in SQL functionality.

1 - Output both stored procs to tables

2 - Run a UNION ALL inside an error trap.

This will immediately tell you if the columns or data types don't line up.

3 - Run an EXCEPT query comparing A to B and then B to A

This will tell you if the actual data in the result sets doesn't match.

You could wrap all this in some dynamic SQL to automate it to run against a large set of procs if you needed.

I have encountered a similar problem and having to optimize some large procedures and wanting to compare the old vs. new for some input sets. The idea is similar to what is already answered, but I thought an already written procedure would be better.

The above version is updated to work with Sql2012 which requires to specify the exact schema of returned resultset, but it can easily be adapted to Sql2008 (no WITH RESULTSETS there).

Here is the code:

-- 
-- Description: compares the results returned by two stored procedures. Comparison is performed using a 'loopback' linked server and using openquery, so the final query
--              must obey openquery limitations. It returns all rows that are within the first result set and not within the second and viceversa. If all result sets are 
--              empty, results are equivalent (order does not matter)
--
-- PARAMS:
--      @Procedure1FullName: procedure 1 full name (i.e. database.schema.proc_name)
--      @Params1Str: procedure 1 params as string (e.g. @param1 = value1, @param2 = 'value2)'
--      @Procedure2FullName: procedure 2 full name
--      @Params2Str: procedure 2 params as string
--      @ResultSetStr: result set column specification (it is required for usage of procedure in SQL 2012+)
--      @LoopBackServerName: loopback (same server) linked server name - required to use openquery on the same server (and database)
--      @Debug: outputs debug info
--
-- CHANGE LOG:
-- Author: alexandru.dragan
-- Date: 20140811
-- Description: added ResultSetStr param as Sql2012 cannot handle execution of procedures without knowing the result set structure
--
--
-- =============================================
ALTER PROCEDURE [dbo].[uspCompareProcedureResults]
(
    @Procedure1FullName VARCHAR(255),
    @Params1Str VARCHAR(MAX),
    @Procedure2FullName VARCHAR(255),
    @Params2Str VARCHAR(MAX),
    @ResultSetStr VARCHAR(MAX),
    @LoopBackServerName VARCHAR(255) = 'loopback',
    @ForceShowDetails BIT = 0,
    @Debug BIT = 0
)
AS
BEGIN
    DECLARE @SQL NVARCHAR(MAX) = ''
    DECLARE @InputStr NVARCHAR(MAX)

    -- escaping string parameters
    SET @Params1Str = REPLACE(@Params1Str, '''', '''''')
    SET @Params2Str = REPLACE(@Params2Str, '''', '''''')

    SET @InputStr = @Procedure1FullName + '(' + @Params1Str + ')'

    SET @SQL = '
        DECLARE @StartTime datetime;
        DECLARE @Diff1 BIGINT;
        DECLARE @Diff2 BIGINT;

        -- executing and measuring time for the first procedure
        SET @StartTime = GETDATE();
        SELECT * INTO #R1
        FROM OPENQUERY(' + @LoopBackServerName + ', ''set fmtonly off exec ' + @Procedure1FullName + ' ' + @Params1Str + ' WITH RESULT SETS (( ' +  @ResultSetStr + '))'');
        SET @Diff1 = DATEDIFF(ms, @StartTime, GETDATE());

        -- executing and measuring time for the second procedure
        SET @StartTime = GETDATE();
        SELECT * INTO #R2
        FROM OPENQUERY(' + @LoopBackServerName + ', ''set fmtonly off exec ' + @Procedure2FullName + ' ' + @Params2Str + ' WITH RESULT SETS (( ' +  @ResultSetStr + '))'');
        SET @Diff2 = DATEDIFF(ms, @StartTime, GETDATE());

        -- changing all float columns to decimal to ensure correct comparison
        DECLARE @InnerSQL NVARCHAR(MAX) = N''''

        select @InnerSQL += ''alter table #R1 alter column '' + QUOTENAME(COLUMN_NAME) + '' DECIMAL(28, 6);''
        FROM tempdb.INFORMATION_SCHEMA.COLUMNS
        where table_name like ''#R1[___]%'' and DATA_TYPE = ''float'';

        EXEC (@InnerSQL);

        SET @InnerSQL = N'''';
        select @InnerSQL += ''alter table #R2 alter column '' + QUOTENAME(COLUMN_NAME) + '' DECIMAL(28, 6);''
        FROM tempdb.INFORMATION_SCHEMA.COLUMNS
        where table_name like ''#R2[___]%'' and DATA_TYPE = ''float'';

        EXEC (@InnerSQL);

        -- creating temporary tables to hold result sets differences
        SELECT ''R1 \ R2'' AS [R1 \ R2], * INTO #R12 
        FROM #R1 
        WHERE 1 = 0

        SELECT ''R2 \ R1'' AS [R2 \ R1], * INTO #R21
        FROM #R1 
        WHERE 1 = 0

        -- inserting data
        INSERT INTO #R12 
        SELECT ''R1 \ R2'' AS [R1 \ R2], * FROM #R1 
        EXCEPT
        SELECT ''R1 \ R2'' AS [R1 \ R2], * FROM #R2;

        INSERT INTO #R21
        SELECT ''R2 \ R1'' AS [R2 \ R1], * FROM #R2 
        EXCEPT
        SELECT ''R2 \ R1'' AS [R2 \ R1], * FROM #R1;

        -- difference flag
        DECLARE @IsDiff BIT = 0
        IF EXISTS (SELECT 1 FROM #R12) OR EXISTS (SELECT 1 FROM #R21)
            SET @IsDiff = 1

        SELECT ''' + @InputStr + ''' AS ''' + LEFT(@InputStr, 128) + ''', @IsDiff AS ''Diff results'', ''R1'' AS [R1], @Diff1 AS ''Duration1 [ms]'', @Diff2 AS ''Duration2 [ms]'';

        -- showing details if a difference exists or details must be output
        if (@IsDiff  = 1 OR ' + CAST(@ForceShowDetails AS VARCHAR) + ' = 1)
        BEGIN
            SELECT ''Results for first procedure'' AS ''Results for first procedure'', * FROM #R1;
            SELECT ''Results for second procedure'' AS ''Results from the second procedure'', * FROM #R2;
            SELECT * FROM #R12
            SELECT * FROM #R21
        END
    '

    if (@Debug = 1)
    BEGIN
        PRINT '@SQL = ' + @SQL
        PRINT 'SQL len = ' + CAST(LEN(@SQL) AS VARCHAR(MAX))
    END

    EXEC (@SQL)
END

Limitations / noted / known issues:

1. requires a linked server to point to the same instance or database
2. the procedure may (and should) return only one result set
3. all floats are converted to decimals (fixed point numerics) in order to avoid nearly equal floats differences

Not sure if you truly mean "across multiple databases" or "across multiple servers". If its a server you'll need to add linked servers.

The stored proc below will compare the output resultset of 2 stored procedures, or 2 statements. Doesn't need to know the schema of the result set, but the 2 input statements must have identical schemas. It will return 0 rows if the output is the same. This solution uses openrowset command in SQL Server. Here is some sample usage of the Stored proc

    -- Sample Usage, works for both Queries and Stored Procs    
    DECLARE @SQL_SP1 VARCHAR(MAX)
    DECLARE @SQL_SP2 VARCHAR(MAX)

    -- Compare results of 2 Stored Procs
    SET @SQL_SP1 = 'EXEC SomeDB.dbo.[usp_GetWithTheProgram_OLD] 100, ''SomeParamX'''
    SET @SQL_SP1 = 'EXEC SomeDB.dbo.[usp_GetWithTheProgram_NEW] 50, ''SomeParamX'''
    EXEC utlCompareStatementResults @SQL_SP1, @SQL_SP2

    -- Compare just 2 SQL Statements
    SET @SQL_SP1 = 'SELECT * FROM SomeDB.dbo.Table1 WHERE CreatedOn > ''2016-05-08'''
    SET @SQL_SP1 = 'SELECT * FROM SomeDB.dbo.Table1 WHERE CreatedOn > ''2016-06-11'''
    EXEC utlCompareStatementResults @SQL_SP1, @SQL_SP2

The SP requires the following prerequisites because it uses openrowset, which may not be ideal for a production environment.

   -- Code uses openrowset so needs some special permissions
   EXEC sp_configure 'show advanced options', 1
   EXEC sp_configure 'ad hoc distributed queries', 1
   EXEC sp_serveroption @@SERVERNAME, 'DATA ACCESS', TRUE

Here is the code for the stored proc.

==================================================================================
    --== SUMMARY utlCompareStatementResults 
    --==    Compares output of 2 queries or stored procs
    --==    - requires sp_configure 'show advanced options', 1
    --==    - requires sp_configure 'ad hoc distributed queries', 1
    --==    - maybe requires EXEC sp_serveroption @@SERVERNAME, 'DATA ACCESS', TRUE
    --==    - requires the RecordSet Output to have Unique ColumnNames (no duplicate columns)
    --==    - requires references in straight SQL to be fully qualified [dbname].[schema].[objects] but not within an SP
    --==    - requires references SP call to be fully qualifed [dbname].[schema].[spname] but not objects with the SP
    --== OUTPUT
    --==    Differences are returned 
    --==    If there is no recordset returned, then theres no differences
    --==    However if you are comparing 2 empty recordsets, it doesn't mean anything
    --== USAGE
    --==   DECLARE @SQL_SP1 VARCHAR(MAX)
    --==   DECLARE @SQL_SP2 VARCHAR(MAX)
    --==   -- Compare just 2 SQL Statements
    --==   SET @SQL_SP1 = 'SELECT * FROM SomeDB.dbo.Table1 WHERE CreatedOn > ''2016-05-08'''
    --==   SET @SQL_SP1 = 'SELECT * FROM SomeDB.dbo.Table1 WHERE CreatedOn > ''2016-06-11'''
    --==   EXEC utlCompareStatementResults @SQL_SP1, @SQL_SP2
    --==
    --==   -- Compare results of 2 Stored Procs
    --==   SET @SQL_SP1 = 'EXEC SomeDB.dbo.[usp_GetWithTheProgram_OLD] 100, ''SomeParamX'''
    --==   SET @SQL_SP1 = 'EXEC SomeDB.dbo.[usp_GetWithTheProgram_NEW] 50, ''SomeParamX'''
    --==   EXEC utlCompareStatementResults @SQL_SP1, @SQL_SP2
    --==================================================================================
    CREATE PROCEDURE utlCompareStatementResults
       @SQL_SP1 VARCHAR(MAX),
       @SQL_SP2 VARCHAR(MAX)
    AS
    BEGIN
        DECLARE @TABLE1 VARCHAR(200)
        DECLARE @TABLE2 VARCHAR(200)
        DECLARE @SQL_OPENROWSET VARCHAR(MAX) 
        DECLARE @CONNECTION VARCHAR(100)

        SET @CONNECTION = 'server='+@@SERVERNAME+';Trusted_Connection=yes'

        SET @SQL_SP1 = REPLACE(@SQL_SP1, '''','''''')
        SET @SQL_SP2 = REPLACE(@SQL_SP2, '''','''''')

        SET @TABLE1 = '#' + SUBSTRING(CONVERT(VARCHAR(250),NEWID()), 1, 8)
        SET @TABLE2 = '#' + SUBSTRING(CONVERT(VARCHAR(250),NEWID()), 1, 8)

        SET @SQL_OPENROWSET =
        'SELECT * ' + ' ' +
        'INTO ' + @TABLE1 + ' ' +
        'FROM OPENROWSET(''SQLNCLI'', ' + '''' + @CONNECTION + '''' +
                        ',''' + @SQL_SP1 +'''); ' +
        'SELECT * ' + ' ' +
        'INTO ' + @TABLE2 + ' ' +
        'FROM OPENROWSET(''SQLNCLI'', ' + '''' + @CONNECTION + '''' +
                        ',''' + @SQL_SP2 +'''); ' +
        '(SELECT * FROM ' + @TABLE1 + ' EXCEPT SELECT * FROM ' + @TABLE2 + ') '  +
        ' UNION ALL ' +
        '(SELECT * FROM ' + @TABLE2 + ' EXCEPT SELECT * FROM ' + @TABLE1 + '); ' +
        'DROP TABLE ' + @TABLE1 + '; ' +
        'DROP TABLE ' + @TABLE2 + '; '
        PRINT @SQL_OPENROWSET
        EXEC (@SQL_OPENROWSET)
        PRINT 'DifferenceCount: ' + CONVERT(VARCHAR(100), @@ROWCOUNT)
    END