如何在DBeaver SQL控制台正确使用动态SQL实现动态透视？

问题 #

现有一个SQL查询，通过透视一张表的数据后与另一张表关联，原查询需硬编码透视项。为避免硬编码，编写了动态透视脚本：

DECLARE @columns AS NVARCHAR(MAX), 
        @sql AS NVARCHAR(MAX);

-- 获取要透视的唯一元素列表
SELECT @columns = ISNULL(@columns + ', ', '') + QUOTENAME(Element)
FROM (SELECT DISTINCT Element FROM Spectro_Spark.dbo.ElementLines) AS Elements;

-- 构建动态透视SQL
SET @sql = '
WITH PivotedElementLines AS (
    SELECT 
        SamplesID, 
        ' + @columns + ' 
    FROM 
        (SELECT 
            SamplesID, 
            Element, 
            Result, 
            StdDev, 
            LimitStatus, 
            CalibrationStatus, 
            Reported, 
            CalibrationMin, 
            CalibrationMax, 
            UpperWarningLimit, 
            LowerWarningLimit
         FROM Spectro_Spark.dbo.ElementLines
         WHERE SamplesID > 145592
        ) AS SourceTable
    PIVOT (
        MAX(Result) FOR Element IN (' + @columns + ')
    ) AS PivotTable
)
SELECT 
    s.SampleName, 
    s.AcquireDate, 
    s.Identifier, 
    s.SamplesID, 
    s.Furnace, 
    s.Shift, 
    s.RecalculationDateTime, 
    s.PourTime, 
    s.PourDate, 
    s.Grade, 
    s.GradeID, 
    p.*
FROM 
    Spectro_Spark.dbo.Samples AS s
JOIN 
    PivotedElementLines AS p
ON 
    s.SamplesID = p.SamplesID;
';

-- 执行动态SQL
EXEC sp_executesql @sql;

在DBeaver 23.2.5.202311191730版本中执行该脚本时，报错：

Error occurred during SQL script execution

Reason:
SQL Error [137] [S0002]: Must declare the scalar variable "@columns".

已在脚本开头声明@columns和@sql变量，但问题依旧，需修正错误使其在DBeaver SQL控制台正常运行。

解决方案 #

这个错误的核心原因是DBeaver默认按;拆分SQL脚本为多个独立批次执行，导致变量声明和后续赋值、使用不在同一个批次里，后续批次无法识别@columns变量。以下是两种可行的修正方法：

方法1：调整DBeaver的语句分隔符 #

• 打开SQL编辑器右上角的齿轮图标，进入编辑SQL格式设置
• 找到语句分隔符选项，将默认的;替换为GO
• 在脚本末尾添加GO作为批次分隔符
• 重新执行整个脚本

方法2：将所有逻辑封装到单个批次（推荐） #

用BEGIN...END包裹整个脚本逻辑，强制DBeaver将所有代码作为一个批次执行，同时修正动态SQL里的HTML转义符>为正常的>：

BEGIN
    DECLARE @columns AS NVARCHAR(MAX), 
            @sql AS NVARCHAR(MAX);

    -- 获取要透视的唯一元素列表
    SELECT @columns = ISNULL(@columns + ', ', '') + QUOTENAME(Element)
    FROM (SELECT DISTINCT Element FROM Spectro_Spark.dbo.ElementLines) AS Elements;

    -- 构建动态透视SQL
    SET @sql = '
    WITH PivotedElementLines AS (
        SELECT 
            SamplesID, 
            ' + @columns + ' 
        FROM 
            (SELECT 
                SamplesID, 
                Element, 
                Result, 
                StdDev, 
                LimitStatus, 
                CalibrationStatus, 
                Reported, 
                CalibrationMin, 
                CalibrationMax, 
                UpperWarningLimit, 
                LowerWarningLimit
             FROM Spectro_Spark.dbo.ElementLines
             WHERE SamplesID > 145592
            ) AS SourceTable
        PIVOT (
            MAX(Result) FOR Element IN (' + @columns + ')
        ) AS PivotTable
    )
    SELECT 
        s.SampleName, 
        s.AcquireDate, 
        s.Identifier, 
        s.SamplesID, 
        s.Furnace, 
        s.Shift, 
        s.RecalculationDateTime, 
        s.PourTime, 
        s.PourDate, 
        s.Grade, 
        s.GradeID, 
        p.*
    FROM 
        Spectro_Spark.dbo.Samples AS s
    JOIN 
        PivotedElementLines AS p
    ON 
        s.SamplesID = p.SamplesID;
    ';

    -- 执行动态SQL
    EXEC sp_executesql @sql;
END

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