How do I make this query as a dynamic stored procedure?

Question

I need this working query as a stored procedure where I can give two databases (one masterdb one testdb), start chainage, end chainage and UniqueRun as input

DECLARE @StartChainage FLOAT = 17.00;
DECLARE @EndChainage FLOAT = 20.00;

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,
        LA25_00230133_Dis_Batch002A.dbo.fn_GetCollectedChainage(DS.IDSession, DR.DistanceStamp) AS Chainage
    FROM LA25_00230133_Dis_Batch002A.distress.DistressRecords DR
    JOIN LA25_00230133_Dis_Batch002A.dbo.DCSessions DS ON DR.IDSession = DS.IDSession
    JOIN LA25_00230133_Dis_Batch002A.distress.DistressTypes DT ON DR.IDDistressType = DT.IDDistressType
    JOIN LA25_00230133_Dis_Batch002A.distress.Severities S ON DR.IDSeverity = S.IDSeverity
    WHERE DS.UniqueRun = '51H0VRTI'
),
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,
        LA25_00230133_Dis_Batch002A_TRN.dbo.fn_GetCollectedChainage(DS.IDSession, DR.DistanceStamp) AS Chainage
    FROM LA25_00230133_Dis_Batch002A_TRN.distress.DistressRecords DR
    JOIN LA25_00230133_Dis_Batch002A_TRN.dbo.DCSessions DS ON DR.IDSession = DS.IDSession
    JOIN LA25_00230133_Dis_Batch002A_TRN.distress.DistressTypes DT ON DR.IDDistressType = DT.IDDistressType
    JOIN LA25_00230133_Dis_Batch002A_TRN.distress.Severities S ON DR.IDSeverity = S.IDSeverity
    WHERE DS.UniqueRun = '51H0VRTI'
)
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

-- Extra Distress
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;

But when I try to make it dynamic, I get tons of repetitive syntax errors which don't seem to resolve

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 = '
    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) + '.dbo.fn_GetCollectedChainage(DS.IDSession, DR.DistanceStamp) AS Chainage
        FROM ' + QUOTENAME(@MasterDB) + '.distress.DistressRecords DR
        JOIN ' + QUOTENAME(@MasterDB) + '.dbo.DCSessions DS ON DR.IDSession = DS.IDSession
        JOIN ' + QUOTENAME(@MasterDB) + '.distress.DistressTypes DT ON DR.IDDistressType = DT.IDDistressType
        JOIN ' + QUOTENAME(@MasterDB) + '.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) + '.dbo.fn_GetCollectedChainage(DS.IDSession, DR.DistanceStamp) AS Chainage
        FROM ' + QUOTENAME(@TestDB) + '.distress.DistressRecords DR
        JOIN ' + QUOTENAME(@TestDB) + '.dbo.DCSessions DS ON DR.IDSession = DS.IDSession
        JOIN ' + QUOTENAME(@TestDB) + '.distress.DistressTypes DT ON DR.IDDistressType = DT.IDDistressType
        JOIN ' + QUOTENAME(@TestDB) + '.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;

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

How do I make this query as a working stored procedure?

The easiest way to debug dynamic SQL is to PRINT/SELECT the statement first. Then you can debug that SQL and solve the problem before propagating the solution to your SQL that generates the dynamic statement. Often you'll find that the problems are quite simple, such as a typographical error that is difficult to spot in the literal strings; for example a missing whitespace/linebreak, or leading/trailing delimiters. Taking the time to get the non-dynamic statement working first is really important, as if that doesn't work the dynamic one will have no chance of working correctly. — Thom A
– Thom A ♦, Commented May 23 at 12:20
With all the single quotes you have to make as double quotes that happens a lot. What I do is NOT try to execute it. I just remove large parts of the query, then print out what the query is. Then validate that part to find the issue with the quotes. Fix it, then add more, and keep going until you have the quote issue fixed. Even without that I ALWAYS print out dynamic SQL before trying to execute it to make sure it is what I actually wanted before trying to execute it. — Brad
– Brad, Commented May 23 at 12:21
Be VERY careful here. You are opening yourself to sql injection the way you have this written. This is really dangerous to just execute a string you receive as a parameter. — Sean Lange
– Sean Lange, Commented May 23 at 14:24
Have they, @SeanLange ? They correctly use QUOTENAME to quote the database names, and their variables are parameterised. I don't actually see any injection issues. The only additional thing they could do would be the validate the names of the databases against sys.databases, and in the event it's not a real database, not run anything, but they would still need use QUOTENAME against the name column instead. — Thom A
– Thom A ♦, Commented May 23 at 14:59
Side note: you should not use the sp_ prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoid sp_ and use something else as a prefix - or no prefix at all! — marc_s
– marc_s, Commented May 23 at 19:16

Thom A · Accepted Answer · 2025-05-23 13:21:08Z

As I mentioned in the comment, if you PRINT or SELECT the value of your dynamic statement, this will very likely tell you what the problem is.

The problem here is truncation; you can see this when you SELECT your variable, as the value is abruptly terminated (at the point of truncation). You are concatenating a lot of literal string values, however, none of these are a MAX length value. Instead they are all non-MAX literal varchars (they should be nvarchars), that are implicitly converted to an nvarchar due the concatenation of non-MAX nvarchar variables (side note, @MasterDB and @TestDB should be sysname). As none of these values are MAX length, then the value cannot exceed 8,000 bytes in size, which is likely to be 4,000 characters; concatenating values that exceed 8,000 bytes does not cause the data engine to implicitly cast/convert the value to a MAX length data type.

The "simple" solution I find is to start your dynamic statement with CONVERT(nvarchar(MAX),N'') +; this results in every latter string concatenated to be implicitly converted to a MAX length value, as MAX has a higher data type precedence. I also, however, suggest correcting all your literal strings to be literal nvarchars, not varchars.

So, instead your SET statement would start like this, and concatenation would like this this:

SET @SQL = CONVERT(nvarchar(MAX),N'') + N'
WITH MasterDistress AS ( 
...
    FROM ' + QUOTENAME(@MasterDB) + N'.distress.DistressRecords DR
...
ORDER BY Chainage;';

Collectives™ on Stack Overflow

How do I make this query as a dynamic stored procedure?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related