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.