为何需两次声明变量？动态SQL与外部变量作用域疑问

你观察到的这个现象，完全是由SQL Server动态SQL的执行机制决定的，咱们来把这个问题拆明白：

核心原因：动态SQL有独立的执行上下文 #

你在脚本顶部声明的@Offset、@TeamCode这些变量，属于外层批处理的执行上下文。而当你通过sp_executesql执行拼接出来的SQL字符串时，这段动态SQL会在一个全新的、和外层完全隔离的内部执行环境里运行。

打个比方：外层脚本是你的主房间，动态SQL是一个带锁的小房间——主房间里的东西（变量）拿不到小房间里，小房间也看不到主房间的东西。所以如果动态SQL里要用到这些变量，你之前的做法是在小房间里重新“复制”一套（重复声明），不然SQL Server会报错说“变量未声明”。

你的写法可以优化：用参数传递代替重复声明 #

其实你完全不用重复声明变量，sp_executesql本身就支持参数传递，这才是处理动态SQL变量的最佳实践——既不用维护两份变量值，还能避免SQL注入风险，甚至可以让执行计划被缓存提升性能。

优化后的代码如下：

DECLARE @columns NVARCHAR(MAX) = ''
DECLARE @sql NVARCHAR(MAX) = ''
DECLARE @Offset int = 0
DECLARE @Limit int = 10
DECLARE @TeamCode NVARCHAR(50) = 'Team1'

SELECT @columns+=QUOTENAME(status_name) + ',' FROM my_db.job_statuses ORDER BY id;
SET @columns = LEFT(@columns, LEN(@columns) - 1);

-- 构造动态SQL时不再重复声明变量，直接使用参数占位符
SET @sql =' SELECT P.*, TSC.TOTAL_STATUS_COUNT
FROM (
 SELECT * FROM (
  SELECT EJI.planning_item_id, S.status_name
  FROM my_db.jobs J
  INNER JOIN my_db.job_statuses S ON J.status = S.ID
  INNER JOIN my_db.extra_job_information EJI ON EJI.job_id = J.job_id
  WHERE (@TeamCode IS NULL OR ASSIGNED_TO LIKE @TeamCode)
  AND (Planning_item_id IS NOT NULL)
  GROUP BY status_name, planning_item_id, J.job_id
 ) T
 PIVOT(
  COUNT(status_name)
  FOR status_name IN ('+ @COLUMNS +')
 ) AS PIVOT_TABLE
 ORDER BY planning_item_id OFFSET @Offset ROWS FETCH NEXT @Limit ROWS ONLY
) P
INNER JOIN (
 SELECT EJI.planning_item_id, COUNT(J.status) TOTAL_STATUS_COUNT
 FROM my_db.extra_job_information EJI
 INNER JOIN my_db.jobs J ON J.JOB_ID = EJI.JOB_ID
 WHERE (@TeamCode IS NULL OR ASSIGNED_TO LIKE @TeamCode)
 AND (Planning_item_id IS NOT NULL)
 GROUP BY planning_item_id
) TSC ON TSC.planning_item_id = P.planning_item_id';

-- 通过sp_executesql的参数列表传递外层变量
EXECUTE sp_executesql 
    @sql,
    N'@Offset int, @Limit int, @TeamCode NVARCHAR(50)', -- 定义参数类型
    @Offset = @Offset, -- 传递外层变量值
    @Limit = @Limit,
    @TeamCode = @TeamCode;

补充：你之前的写法为什么能运行？ #

你之前在动态SQL里重复声明变量，相当于在内部执行环境里创建了全新的变量实例——它和外层的变量只是名字相同，本质是两个完全独立的东西。比如如果你把外层的@TeamCode改成'Team2'，动态SQL里依然会用'Team1'，因为它用的是自己内部声明的那个变量值。

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