在存储过程中使用exec @sql和临时表

Question

我有一个存储过程，其中部分如下：

@DRange是传入的varchar值。

declare @sql varchar(max)
set @sql = 'select * into #tmpA from TableA where create_date >= getDate - ' + @DRange + '' and is_enabled = 1'

exec (@sql)

select * from #tmpA

当我执行存储过程时，出现了错误信息：“无法找到对象“#tmpA”，因为它不存在或您没有权限。”

是不是不可能使用临时表并执行它，还是我做错了什么？

- William Tang

---

为什么要使用动态SQL？难道不能使用普通查询 select * into #tmpA from TableA where create_date >= getDate - @DRange and is_enabled = 1 吗？如果需要，可以将@DRange转换为不同的类型。 - Yuriy Galanter

因为我需要将其他参数，例如数据库名称，合并到查询中。 - William Tang

Answer 1

#tmpA 是在不同的作用域中创建的，因此在动态SQL之外不可见。您可以将最终的 SELECT 作为动态SQL的一部分。还有其他几点要注意：

• 创建/引用对象时始终使用架构前缀
• 动态SQL时始终使用sp_executesql；在这种情况下，它允许您对@DRange值进行参数化并避免SQL注入风险。
• 始终使用N前缀的Unicode字符串 - Unicode对于sp_executesql是必需的，但如果您在代码的其他区域变懒了，这也可能导致痛苦的隐式转换。

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'select * into #tmpA from dbo.TableA 
    where create_date >= DATEADD(DAY, -@DRange, GETDATE())
    AND is_enabled = 1; SELECT * FROM #tmpA';

EXEC sp_executesql @sql, N'@DRange INT', @DRange;

当然，如果你只是在选择数据，我很难理解为什么这首先是动态SQL。我假设你的查询（或者之后对临时表的操作）比这更复杂 - 如果是这样，请不要为了让我们理解而简化查询。告诉我们你整个问题会避免很多来回沟通，因为额外的细节可能会改变答案。

- Aaron Bertrand

---

感谢您的评论，我已经使用了##tmpA而不是#tmp，问题已经解决。 - William Tang

@WilliamTang，没有。你知道 ##tmpA 是什么吗？它创建一个全局临时表。无论你的日期范围或数据库参数是什么，当两个人同时运行此存储过程时会发生什么事情呢？ - Aaron Bertrand

Answer 2

这是我的建议：

declare @sql varchar(max)

set @sql = 'select * from TableA where create_date >= getDate - ' + @DRange + '' and is_enabled = 1'

Select * Into #tmpA from TableA where create_date = '01/01/1000' -- to create a blank table

insert into #tmpA

exec (@sql)

select * from #tmpA

- Sani