Some of the tasks in my team are related to stored procedure optimization. I am mainly a .NET developer, but I thought of writing a generic procedure for data comparison while procedures are being changed. The main goals are:

- ensure that changes to not break existing functionalities (same inputs should provide same output)
- can be run from time to time to easy spot bugs introduced by optimizations
- should also provide basic profiling information, so that "optimized" version is actually faster
- should be run from SQL Server 

**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
    --
    -- =============================================
    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

**Call example:**

    declare @paramsStr VARCHAR(max) = '@year=2014,@month=6'
    declare @resultSetStr VARCHAR(MAX) = 'kpi_id INT, kpi_value NUMERIC(18, 2)'
    exec uspCompareProcedureResults 
        @Procedure1FullName = '[loopback].[DB].[usr].[get_data]', @Params1Str = @paramsStr, 
        @Procedure2FullName = '[loopback].[DB].[usr].[get_data_next_gen]', @Params2Str = @paramsStr, 
        @ResultSetStr = @resultSetStr, @ForceShowDetails = 0, @Debug = 1
    GO 

**Limitations / notes / known issues:**

- Requires a linked server to point to the same instance (used by `openquery`)
- The procedure must return only one result set
- All floats are converted to decimals (fixed point numbers) in order to avoid minor float differences
- The procedure can be adapted to also run in SQL Server 2008 (just remove WITH RESULTS SETS)

The procedure does its work, but I am wondering: **are there easier/better options of achieving the comparison**.