SQL Server 2012 在sp_executesql 中生成的临时表的可见性

在sql存储过程中，经常使用到动态sql语句，写法类似于这样

Set @strParameter=N'@StartTime datetime,@EndTime datetime'
Exec sp_executesql @strSql,@strParameter,@StartTime,@EndTime    

为了满足业务需求，我们经常会在存储过程中使用到临时表。根据作用域的不同，分为全局临时表和用户临时表。

如果在动态sql语句中构造了用户临时表，代码如下：

exec SP_EXECUTESQL N'SELECT * INTO #temp FROM TestTable' 
SELECT * FROM #temp

执行后会报如下错误

消息 208，级别 16，状态 0，第 37 行
对象名 '#temp' 无效。

在ssms中调试，执行到该动态SQL语句时
会出现异常“未将对象设置引用到对象实例”
这是由于临时表只存在于动态sql这个作用域内，也就是只在动态SQL可见，在当前存储过程中是不可见的，所以会出现找不到该临时表的错误。

首先创建测试表

IF object_id('TestTable') IS NOT NULL
    DROP TABLE TestTable
GO

CREATE TABLE TestTable(id INT IDENTITY(1,1),Info VARCHAR(10))
GO
INSERT TestTable SELECT 'a'
UNION ALL SELECT 'b'
GO

然后依次执行以下三个脚本

脚本一：

EXEC('SELECT * INTO #temp FROM TestTable')
SELECT * FROM #temp

脚本二：

exec SP_EXECUTESQL N'SELECT * INTO #temp FROM TestTable' 
SELECT * FROM #temp

脚本三：

SELECT * INTO #temp FROM TestTable
EXEC('SELECT * FROM #temp')

–DROP TABLE #temp

执行后发现只有脚本三是成功的，其他连个脚本执行后都会提示：

Msg 208, Level 16, State 0, Line 2
Invalid object name '#temp'.

知道了问题出现的原因，解决方案很简单，将用户临时表替换为全局临时表就ok了，也就是在#temp前再加个‘#’，即 ##Temp