SQL Server动态查询报错：需声明标量变量@RegionXML与@oXml

报错的核心原因是动态SQL的执行上下文和存储过程本身的上下文完全独立——你在@WhereClause里直接引用了存储过程内部定义的@RegionXML和@oXml变量，但动态SQL执行时无法识别这些仅存在于存储过程局部的变量，因此触发"未声明标量变量"的错误。

另外你的代码还有两个隐含问题：

• 存储过程中未定义@CompanyId变量，但动态SQL里直接使用了cast(@CompanyId as varchar(100))，这会导致额外报错
• 代码末尾重复执行了两次EXECUTE (@FinalSet)，属于冗余操作

推荐采用参数化动态SQL的方式解决，既可以消除变量未声明的问题，还能避免SQL注入风险，同时修复其他隐含问题。

修改后的完整存储过程代码 #

ALTER PROCEDURE [Proc_MyStored_Procedure]
    @OrgUnitXml varchar(max),
    @RegXml varchar(max),
    @CompanyId int -- 新增参数：原代码中用到但未声明的变量
AS
BEGIN
    SET NOCOUNT ON;
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

    DECLARE @FinalSet nvarchar(max) ,          
            @WhereClause nvarchar(800),
            @oXml xml = @OrgUnitXml,
            @RegionXML xml

    SET @RegionXML = CASE
                         WHEN @RegXml = '' 
                             THEN '<Data><Rec><Id>0</Id></Rec></Data>'
                             ELSE @RegXml
                     END

    -- 构建Where子句，使用参数占位符而非直接引用局部变量
    IF (@RegXml <> '')
        SET @WhereClause = N' and  r.RegionId in (  select nref.value(''Id[1]'', ''bigint'') as RegionId
        from @RegionXML.nodes(''//Data/Rec'') as R(nref)  )   and OrgUnitId in(select nref.value(''Id[1]'', ''int'') as OrgUnitId
        from @oXml.nodes(''//Data/Rec'') as R(nref))';
    ELSE
        SET @WhereClause = N' and OrgUnitId in(select nref.value(''Id[1]'', ''int'') as OrgUnitId
        from @oXml.nodes(''//Data/Rec'') as R(nref))';

    -- 构建动态SQL语句，统一使用nvarchar类型避免Unicode字符问题
    SET @FinalSet = N'Select  EmployeeId,CompanyId,[FullName], EmailAddress, Org AS [Department], CAST( GETDATE() AS Date ) as [ReportDate],MAX(pv.Allowance) AS [Allowance], MAX(PV.[Annual Gross]) AS [AnnualGross],
Max(pv.Bonus) As [Bonus],Max(pv.Commission) As [Commission],Max(pv.[Medical - Contribution]) As [MedicalContribution], Max(pv.[Medical Employer Deductions]) As [MedicalEmployerDeductions], Max(pv.Other) As [Other], Max(pv.Overtime) As [Overtime], Max(pv.Pension) As [Pension], Max(pv.[Standby Allowance]) As [StandbyAllowance]
    
from
(
    select st.SalaryTypeId, st.CompanyId, st.SalaryTypeName as SalaryTypeName ,es.SalaryAmount,es.EmployeeId, emp.FirstName + '' '' + emp.MiddleName + '' ''+  emp.LastName as [FullName], emp.EmailAddress , cs.Org,es.DateCreated
    from  SalaryType as st 
          left outer join (select EmployeeSalaryId, EmployeeId, SalaryTypeId, SalaryAmount, Active,DateCreated
                            from EmployeeSalary where Active = 1 ) as es
    on st.SalaryTypeId = es.SalaryTypeId    
    LEFT OUTER JOIN  Employee AS emp on es.EmployeeId = emp.EmployeeId

    LEFT OUTER JOIN    CS AS cs on emp.OrgUnitId = cs.ID

    INNER JOIN Region as r on st.CompanyId = r.CompanyId
        where st.CompanyId = @CompanyId and st.Active = 1   and es.Active = 1  ' + @WhereClause + N'
) as q
Pivot(
  AVG(SalaryAmount)
  for SalaryTypeName in(    [Allowance],
                            [Annual Gross],
                            [Bonus],
                            [Commission],
                            [Medical - Contribution],
                            [Medical Employer Deductions],
                            [Monthly Gross],
                            [Monthly Net],
                            [Other],
                            [Overtime],
                            [Pension],
                            [Standby Allowance])
) as pv
where EmployeeId is not null
GROUP BY EmployeeId,CompanyId,[FullName],EmailAddress,Org 
Order by [FullName]';

    -- 使用sp_executesql传递参数，替代直接EXECUTE
    EXEC sp_executesql @FinalSet,
        N'@CompanyId int, @oXml xml, @RegionXML xml',
        @CompanyId = @CompanyId,
        @oXml = @oXml,
        @RegionXML = @RegionXML;
END

1. 参数化动态SQL：用sp_executesql替代EXECUTE，可以将存储过程的局部变量作为参数传递给动态SQL，让动态SQL能识别这些变量，同时避免SQL注入风险。
2. 修复未定义变量：新增@CompanyId作为存储过程的输入参数，解决原代码中变量未声明的问题。
3. 移除冗余执行：删除了一次重复的EXECUTE (@FinalSet)调用。
4. 统一字符串类型：动态SQL相关变量全部使用nvarchar类型，避免处理Unicode字符时出现异常。

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