必须声明标量变量错误

这与我最后一个问题有关。我有这个代码：

DECLARE @PatientId UNIQUEIDENTIFIER
DECLARE @FormId NCHAR(16)
DECLARE @columns VARCHAR(8000)
DECLARE @query VARCHAR(8000)

SET @PatientId = '3387F956-EA0A-4660-8647-1096E25A3C6F'
SET @FormId = 'form111'

SELECT @columns = COALESCE(
        @columns + ',[' + RTRIM(AttributeGroup) + '_' + RTRIM(AttributeId) + ']',
        '[' + RTRIM(AttributeGroup) + '_' + RTRIM(AttributeId) + ']'
    )
FROM DVItems
WHERE PatientId = @PatientId AND FormId = @FormId
GROUP BY AttributeGroup, AttributeId

SET @query = '
SELECT * 
FROM( 
    SELECT
        PatientId,
        RTRIM(AttributeGroup) + ''_'' + RTRIM(AttributeId) AS ColName,
        FormId,
        Description
    FROM DVItems
    WHERE PatientId = @PatientId AND FormId = @FormId
) p
PIVOT
(
    MAX(Description)
    FOR [ColName]
    IN (' + @columns + ')
) AS p'

EXECUTE (@query)

当我执行这个的时候。它说“必须声明标量变量“@PatientId”。”但是，当我注释掉为 @PatientId 和 @FormId 设置值的行并执行它时，它显示“命令成功完成”。有什么帮助吗？谢谢。尝试这个fiddle.

尝试更改此声明 -

SELECT @query = '
SELECT * 
FROM( 
    SELECT
        PatientId,
        RTRIM(AttributeGroup) + ''_'' + RTRIM(AttributeId) AS ColName,
        FormId,
        Description
    FROM dbo.DVItems
    WHERE PatientId = ''' + CAST(@PatientId AS VARCHAR(36)) + ''' 
        AND FormId = ''' + @FormId + '''
) p
PIVOT
(
    MAX(Description)
    FOR [ColName] IN (' + @columns + ')
) AS p'

完整示例 -

DECLARE 
      @PatientId UNIQUEIDENTIFIER
    , @FormId NCHAR(16)
    , @columns VARCHAR(MAX)
    , @query NVARCHAR(MAX)

SELECT 
      @PatientId = '3387F956-EA0A-4660-8647-1096E25A3C6F'
    , @FormId = 'form111'

SELECT @columns = STUFF((
    SELECT DISTINCT ', [' + RTRIM(AttributeGroup) + '_' + RTRIM(AttributeId) + ']'
    FROM dbo.DVItems
    WHERE PatientId = @PatientId
        AND FormId = @FormId
    FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 2, '')

SELECT @query = '
SELECT * 
FROM( 
    SELECT
        PatientId,
        RTRIM(AttributeGroup) + ''_'' + RTRIM(AttributeId) AS ColName,
        FormId,
        Description
    FROM dbo.DVItems
    WHERE PatientId = ''' + CAST(@PatientId AS VARCHAR(36)) + ''' 
        AND FormId = ''' + @FormId + '''
) p
PIVOT
(
    MAX(Description)
    FOR [ColName] IN (' + @columns + ')
) AS p'

EXEC sys.sp_executesql @query

Output -

PatientId                            FormId           test_01       test_02      test_03
------------------------------------ ---------------- ------------- ------------ ------------
3387F956-EA0A-4660-8647-1096E25A3C6F form111          Test only     Test only    Test only