Currently I have an SQL query where I am pivoting the values of one table and then joining that data with another table. The query works but the problem is that I had to hardcode the pivoting for each individual row I was trying to pivot. Now I am working on trying to improve the query by using dynamic pivoting in order to avoid having to hardcode each individual pivot. To do that I have come up with the following query:

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

-- Get the list of distinct elements to be pivoted
SELECT @columns = ISNULL(@columns + ', ', '') + QUOTENAME(Element)
FROM (SELECT DISTINCT Element FROM Spectro_Spark.dbo.ElementLines) AS Elements;

-- Construct the dynamic SQL for pivoting
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;
';

-- Execute the dynamic SQL
EXEC sp_executesql @sql;

The query looks well-structured to me, but the problem is that when I try running this query I am getting an error every time. This is the error outputted by the SQL console:

    Error occurred during SQL script execution

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

For context I am trying to run this query on Dbeaver Version 23.2.5.202311191730. I have declared both @columns and @sql at the beginning of the script and have looked at countless different publications on how to properly define these variables with no success. What is the proper way to fix this error and have it work within Dbeaver’s SQL console?

Any help or guide in the right direction would be greatly appreciated. Thank you.