用于结果集比较的通用存储过程

我团队中的一些任务与存储过程优化有关。我主要是一名 .NET 开发人员，但我想在更改过程时编写一个用于数据比较的通用过程。主要目标是：

• 确保更改不会破坏现有功能（相同的输入应提供相同的输出）
• 可以不时运行以轻松发现优化引入的错误
• 还应该提供基本的分析信息，这样“优化”版本实际上更快
• 应该从 SQL Server 运行

编码：

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

调用示例：

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 

限制/注意事项/已知问题：

• 要求链接服务器指向同一个实例（由使用openquery）
• 该过程必须只返回一个结果集
• 所有浮点数都转换为小数（定点数）以避免微小的浮点数差异
• 该过程也可以在 SQL Server 2008 中运行（只需删除 WITH RESULTS SETS）

该程序可以正常工作，但我想知道：是否有更简单/更好的选择来实现比较。