SSRS多值GUID参数查询报错，手动输入GUID可正常运行

问题原因 #

当SSRS多值参数传入嵌套子查询的IN (@Contract)条件时，SQL Server无法正确解析参数：多值参数本质是表值参数，但子查询中把它当作标量变量处理，导致语法错误，触发"未声明标量变量@Contract"及"AS关键字附近语法错误"的报错。单值场景下参数是标量，手动写多个GUID是合法字符串列表，因此能正常运行。

解决方法 #

方法1：重写查询为JOIN聚合（推荐） #

将嵌套子查询的聚合逻辑提取为独立子查询，通过JOIN关联主表，避免在子查询中直接使用IN (@Contract)：

SELECT DISTINCT      
    asm.AssemblyCode,
    asm.AssemblyRestorationDesc,
    asm.AssemblyUnit,
    asm.AssemblyGuid,
    COALESCE(m.TotalQuantity, 0) AS Quantity,
    (SELECT SUM(smkr.MarkerQuantity) 
     FROM BaselineMarker smkr  
     WHERE smkr.AssemblyCode = asm.AssemblyCode
       AND smkr.MarkerExcludeFromScope = 'False'
       AND smkr.Deleted = 0) AS BaselineQuantity,
    (SELECT SUM(sa.AllowanceQuantity) 
     FROM BaselineAllowance sa
     WHERE sa.AssemblyCode = asm.AssemblyCode
       AND sa.Deleted = 0) AS AllowanceQuantity
FROM 
    Assembly asm
LEFT JOIN (
    SELECT m.AssemblyGuid, SUM(m.MarkerQuantity) AS TotalQuantity
    FROM Marker m 
    WHERE m.MarkerExcludeFromScope = 'False'
      AND m.Deleted = 0
      AND m.ContractGuid IN (@Contract)
    GROUP BY m.AssemblyGuid
) m ON asm.AssemblyGuid = m.AssemblyGuid
WHERE 
    asm.Deleted = 0
ORDER BY 
    asm.AssemblyCode, asm.AssemblyRestorationDesc, asm.AssemblyUnit, asm.AssemblyGuid

方法2：用STRING_SPLIT处理参数（SQL Server 2016+适用） #

1. 在SSRS数据集的参数设置中，将@Contract的参数值改为：=JOIN(Parameters!Contract.Value, ",")（把多值拼接成逗号分隔的字符串）
2. 修改子查询中的条件为：

AND m.ContractGuid IN (SELECT TRIM(value) FROM STRING_SPLIT(@Contract, ','))

方法3：使用动态SQL拼接查询 #

1. 将查询改为动态SQL形式：

DECLARE @SQL NVARCHAR(MAX)
SET @SQL = N'
SELECT DISTINCT      
    asm.AssemblyCode,
    asm.AssemblyRestorationDesc,
    asm.AssemblyUnit,
    asm.AssemblyGuid,
    (SELECT SUM(m.MarkerQuantity) 
     FROM Marker m 
     WHERE m.AssemblyGuid = asm.AssemblyGuid
       AND m.MarkerExcludeFromScope = ''False''
       AND m.ContractGuid IN (' + @Contract + ')                    
       AND m.Deleted = 0) AS Quantity,
    (SELECT SUM(smkr.MarkerQuantity) 
     FROM BaselineMarker smkr  
     WHERE smkr.AssemblyCode = asm.AssemblyCode
       AND smkr.MarkerExcludeFromScope = ''False''
       AND smkr.Deleted = 0) AS BaselineQuantity,
    (SELECT SUM(sa.AllowanceQuantity) 
     FROM BaselineAllowance sa
     WHERE sa.AssemblyCode = asm.AssemblyCode
       AND sa.Deleted = 0) AS AllowanceQuantity
FROM 
    Assembly asm
WHERE 
    asm.Deleted = 0
ORDER BY 
    asm.AssemblyCode, asm.AssemblyRestorationDesc, asm.AssemblyUnit, asm.AssemblyGuid'

EXEC sp_executesql @SQL

2. 在SSRS数据集的参数设置中，将@Contract的参数值改为：=JOIN(Parameters!Contract.Value, "','")（拼接成带单引号的GUID列表）

内容的提问来源于stack exchange，提问作者E. A. Bagby