在存储过程中使用带有exec @sql的临时表

I have a stored procedure and part of them as below: @DRange is a incoming varchar value

我有一个存储过程，其中一部分如下所示：@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

The problem is when I execute the stored procedure, an error message occurs: Cannot find the object "#tmpA" because it does not exist or you do not have permissions.

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

Is it not possible to use temp table and execute it or did I do something wrong?

是不是可以使用临时表并执行它或我做错了什么？

2 个解决方案

#1

#tmpA is created in a different scope, so is not visible outside of the dynamic SQL. You can just make the ultimate SELECT a part of the dynamic SQL. Also a couple of other things:

#tmpA是在不同的范围内创建的，因此在动态SQL之外是不可见的。您可以将最终的SELECT作为动态SQL的一部分。还有其他一些事情：

Always use the schema prefix when creating/referencing objects
创建/引用对象时始终使用模式前缀
Always use sp_executesql for dynamic SQL; in this case it allows you to parameterize the @DRange value and avoid SQL injection risks.
始终将sp_executesql用于动态SQL;在这种情况下，它允许您参数化@DRange值并避免SQL注入风险。
Always prefix Unicode strings with N - Unicode is required for sp_executesql but if you get lazy about this in other areas of your code it can also lead to painful implicit conversions.
始终使用N前缀Unicode字符串sp_executesql需要Unicode，但如果您在代码的其他区域中对此产生了懒惰，则还可能导致痛苦的隐式转换。

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;

Of course if all you're doing is selecting, I have a hard time understanding why this is dynamic SQL in the first place. I assume your query (or what you later do with the temp table) is more complicated than this - if so, don't dumb it down for us. Telling us your whole problem will prevent a lot of back and forth, as the additional details could change the answer.

当然，如果你所做的只是选择，我很难理解为什么这是动态SQL。我假设你的查询（或者你以后用临时表做的事情）比这更复杂 - 如果是这样的话，不要为我们愚蠢。告诉我们你的整个问题会阻止大量的来回，因为额外的细节可能会改变答案。

#2

Here's what I'd do.

这就是我要做的。

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

#1