执行@roles生成的查询并写入@table却返回0行受影响的问题

我最近写了一段SQL，想通过变量@roles动态生成查询语句，再把执行结果存入表变量@table里，但执行后一直显示(0 行受影响)，完全没拿到预期的数据。我的代码如下：

DECLARE @roles NVARCHAR(MAX) 
SET @roles=' ' 
DECLARE @table TABLE ( Label NVARCHAR(MAX) ) 
select @roles=@roles+ 'Select '+isnull(er.ColumnName,'*')+' from '+er.SchemaName+'.'+er.TableName+' where ' +kcu.COLUMN_NAME +'='+er1.ValueId 
from [Function].[Role] er 
left outer join INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc on tc.TABLE_NAME=er.TableName and tc.TABLE_SCHEMA=er.SchemaName 
left outer join INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu on kcu.CONSTRAINT_NAME=tc.CONSTRAINT_NAME 
LEFT OUTER JOIN Employee_Role er1 ON er.EntityRoleId = er1.RoleId 
LEFT OUTER JOIN Employee e ON er1.EmployeeId = e.EmployeeId 
where e.EmployeeId=54 AND tc.CONSTRAINT_TYPE='PRIMARY KEY' 
INSERT INTO @table EXEC sp_executesql @roles;

可能的原因和排查方向 #

• 动态SQL拼接有问题：最常见的就是拼接出来的语句本身有语法错误或者逻辑错误。比如er1.ValueId如果是字符串类型，拼接时没加单引号，WHERE条件就会失效；或者表名、字段名拼接时出现了空格、拼写错误。建议在执行INSERT之前加一句PRINT @roles，把生成的SQL打印出来，复制到查询窗口单独运行，看看能不能得到数据，有没有报错。
• 生成动态SQL的查询本身无结果：如果用来拼接@roles的SELECT语句根本没查到任何数据，那@roles就还是初始的空格，执行空语句自然不会有行受影响。可以单独运行这段SELECT语句，确认是否存在符合条件的记录（EmployeeId=54、关联到有主键约束的Role数据）。
• 表变量结构不匹配：@table只有一个Label列，但动态生成的SELECT可能返回多列，或者列的类型和Label不兼容，这时候插入会失败，导致0行受影响。要确认动态查询返回的列是否和@table的结构完全匹配。
• 权限不足：执行这段动态SQL的账号，有没有访问er.SchemaName+'.'+er.TableName这些目标表的权限？如果没有权限，查询会返回空结果，甚至可能静默失败。

快速排查步骤 #

1. 在INSERT INTO @table之前添加PRINT @roles;，查看最终生成的SQL语句，手动执行验证。
2. 单独运行拼接@roles的SELECT语句，检查是否有输出行。
3. 如果er1.ValueId是字符串类型，修改拼接逻辑，给它加上单引号：+''''+er1.ValueId+''''（SQL里用两个单引号转义一个单引号）。
4. 确认动态查询的返回列和@table的结构一致，必要时调整表变量的定义。

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