insert exec 语句不能嵌套问题
错误提示:An INSERT EXEC statement cannot be nested.
原因是被调用的存储过程中存在过多exec写法
例如:
存储过程1:test_proc1
set @sql = 'select * from bill '
insert into #bill(id,total)
exec (@sql)
set @sql2 = 'select * from #bill ...'
exec (@sql2)
存储过程2:test_proc2
insert into a
exec test_proc1
解决方案:减少exec 使用,尽量先拼接好语句再执行exec
例如:
set @sql = 'select * from bill '
set @sql2 = ' select * from bill2 a inner join ('+@sql+') b on a.id = b.id '
exec (@sql2)