在存储过程中使用带有 exec @sql 的临时表 [英] Using temp table with exec @sql in stored procedure

问题描述

我有一个存储过程，其中一部分如下:@DRange 是传入的 varchar 值

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

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"，因为它不存在或您没有权限.

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.

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

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

推荐答案

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

#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:

• 始终使用模式前缀创建/引用对象时
• 始终使用 sp_executesql 用于动态 SQL;在这种情况下，它允许您参数化 @DRange 值并避免 SQL 注入风险.
• 总是用 N 作为 Unicode 字符串的前缀 - sp_executesql 需要 Unicode，但如果你在代码的其他区域懒惰，它也会导致痛苦的隐式转换.

• 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.
• 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.

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.我假设您的查询(或您稍后对临时表执行的操作)比这更复杂 - 如果是这样，请不要为我们愚蠢.告诉我们你的整个问题将避免很多来回，因为额外的细节可能会改变答案.

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.

这篇关于在存储过程中使用带有 exec @sql 的临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！