如何将Distress对比查询改为支持动态传参的可用存储过程？

问题背景 #

需将可正常运行的Distress对比查询改造为支持输入主数据库（MasterDB）、测试数据库（TestDB）、起始Chainage、结束Chainage、UniqueRun参数的存储过程，但当前编写的动态存储过程出现语法错误：

Msg 105, Level 15, State 1, Line 183
Unclosed quotation mark after the character string 'Dis'.
Msg 102, Level 15, State 1, Line 183
Incorrect syntax near 'Dis'.

错误原因 #

1. 动态SQL中保留了HTML转义符>、<，SQL Server无法识别这些符号，需替换为原生的>、<运算符
2. 部分字符串拼接处的单引号转义不规范，导致SQL语句截断

修正后的存储过程实现 #

CREATE PROCEDURE sp_CompareDistressMasterVsTest
    @MasterDB NVARCHAR(100),
    @TestDB NVARCHAR(100),
    @UniqueRun NVARCHAR(50),
    @StartChainage FLOAT,
    @EndChainage FLOAT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @SQL NVARCHAR(MAX);

    SET @SQL = N'
    WITH MasterDistress AS (
        SELECT 
            DR.IDDistressRecord,
            DS.IDSession,
            DT.DistressTypeName,
            S.SeverityName,
            geometry::STPolyFromText(
                ''POLYGON(('' + 
                CAST(DR.StartX AS VARCHAR(20)) + '' '' + CAST(DR.DistanceStamp AS VARCHAR(20)) + '', '' +
                CAST(DR.EndX AS VARCHAR(20))   + '' '' + CAST(DR.DistanceStamp AS VARCHAR(20)) + '', '' +
                CAST(DR.EndX AS VARCHAR(20))   + '' '' + CAST(DR.DistanceStamp + DR.Length AS VARCHAR(20)) + '', '' +
                CAST(DR.StartX AS VARCHAR(20)) + '' '' + CAST(DR.DistanceStamp + DR.Length AS VARCHAR(20)) + '', '' +
                CAST(DR.StartX AS VARCHAR(20)) + '' '' + CAST(DR.DistanceStamp AS VARCHAR(20)) +
                ''))'', 0
            ).MakeValid() AS geom,
            ' + QUOTENAME(@MasterDB) + N'.dbo.fn_GetCollectedChainage(DS.IDSession, DR.DistanceStamp) AS Chainage
        FROM ' + QUOTENAME(@MasterDB) + N'.distress.DistressRecords DR
        JOIN ' + QUOTENAME(@MasterDB) + N'.dbo.DCSessions DS ON DR.IDSession = DS.IDSession
        JOIN ' + QUOTENAME(@MasterDB) + N'.distress.DistressTypes DT ON DR.IDDistressType = DT.IDDistressType
        JOIN ' + QUOTENAME(@MasterDB) + N'.distress.Severities S ON DR.IDSeverity = S.IDSeverity
        WHERE DS.UniqueRun = @UniqueRun
    ),
    TestDistress AS (
        SELECT 
            DR.IDDistressRecord,
            DT.DistressTypeName,
            S.SeverityName,
            geometry::STPolyFromText(
                ''POLYGON(('' + 
                CAST(DR.StartX AS VARCHAR(20)) + '' '' + CAST(DR.DistanceStamp AS VARCHAR(20)) + '', '' +
                CAST(DR.EndX AS VARCHAR(20))   + '' '' + CAST(DR.DistanceStamp AS VARCHAR(20)) + '', '' +
                CAST(DR.EndX AS VARCHAR(20))   + '' '' + CAST(DR.DistanceStamp + DR.Length AS VARCHAR(20)) + '', '' +
                CAST(DR.StartX AS VARCHAR(20)) + '' '' + CAST(DR.DistanceStamp + DR.Length AS VARCHAR(20)) + '', '' +
                CAST(DR.StartX AS VARCHAR(20)) + '' '' + CAST(DR.DistanceStamp AS VARCHAR(20)) +
                ''))'', 0
            ).MakeValid() AS geom,
            ' + QUOTENAME(@TestDB) + N'.dbo.fn_GetCollectedChainage(DS.IDSession, DR.DistanceStamp) AS Chainage
        FROM ' + QUOTENAME(@TestDB) + N'.distress.DistressRecords DR
        JOIN ' + QUOTENAME(@TestDB) + N'.dbo.DCSessions DS ON DR.IDSession = DS.IDSession
        JOIN ' + QUOTENAME(@TestDB) + N'.distress.DistressTypes DT ON DR.IDDistressType = DT.IDDistressType
        JOIN ' + QUOTENAME(@TestDB) + N'.distress.Severities S ON DR.IDSeverity = S.IDSeverity
        WHERE DS.UniqueRun = @UniqueRun
    )

    SELECT 
        M.Chainage,
        ROUND(
            ISNULL(
                M.geom.STIntersection(T.geom).STArea() / NULLIF(M.geom.STUnion(T.geom).STArea(), 0), 
                0
            ) * 100, 1
        ) AS AccuracyPercent,
        CASE 
            WHEN T.geom IS NULL THEN ''Distress Missing''
            WHEN T.geom.STArea() > M.geom.STArea() * 1.5 THEN ''Area Mismatch''
            WHEN M.geom.STIntersection(T.geom).STArea() / NULLIF(M.geom.STUnion(T.geom).STArea(), 0) * 100 < 50 THEN ''Area Mismatch''
            WHEN M.DistressTypeName <> T.DistressTypeName THEN ''Distress Type Mismatch''
            WHEN M.SeverityName <> T.SeverityName THEN ''Distress Severity Mismatch''
            ELSE ''-''
        END AS DefectType,
        CASE 
            WHEN T.geom IS NULL THEN ''Distress Not Marked''
            WHEN T.geom.STArea() > M.geom.STArea() * 1.5 THEN ''Distress area larger than expected''
            WHEN M.geom.STIntersection(T.geom).STArea() / NULLIF(M.geom.STUnion(T.geom).STArea(), 0) * 100 < 50 THEN ''Distress area too small or off-target''
            WHEN M.DistressTypeName <> T.DistressTypeName THEN ''Distress Type '' + M.DistressTypeName + '' expected''
            WHEN M.SeverityName <> T.SeverityName THEN ''Distress Severity '' + M.SeverityName + '' expected''
            ELSE ''--No issues--''
        END AS Issue
    FROM MasterDistress M
    OUTER APPLY (
        SELECT TOP 1 T.*
        FROM TestDistress T
        WHERE T.geom.STIsValid() = 1
          AND M.geom.STIntersects(T.geom) = 1
        ORDER BY M.geom.STIntersection(T.geom).STArea() / NULLIF(M.geom.STUnion(T.geom).STArea(), 0) DESC
    ) T
    WHERE M.Chainage BETWEEN @StartChainage AND @EndChainage

    UNION ALL

    SELECT 
        T.Chainage,
        0 AS AccuracyPercent,
        ''Extra Distress'' AS DefectType,
        ''Distress marked unnecessarily'' AS Issue
    FROM TestDistress T
    WHERE NOT EXISTS (
        SELECT 1
        FROM MasterDistress M
        WHERE M.geom.STIntersects(T.geom) = 1
    )
    AND T.Chainage BETWEEN @StartChainage AND @EndChainage

    ORDER BY Chainage;
    '

    EXEC sp_executesql 
        @SQL,
        N'@UniqueRun NVARCHAR(50), @StartChainage FLOAT, @EndChainage FLOAT',
        @UniqueRun = @UniqueRun,
        @StartChainage = @StartChainage,
        @EndChainage = @EndChainage;
END;

调用示例 #

EXEC sp_CompareDistressMasterVsTest 
    @MasterDB = 'LA25_00230133_Dis_Batch002A',
    @TestDB = 'LA25_00230133_Dis_Batch002A_TRN',
    @UniqueRun = '51H0VRTI',
    @StartChainage = 17.0,
    @EndChainage = 20.0;

内容的提问来源于stack exchange，提问作者anonymous123