Using temp table with exec @sql in stored procedure

Question

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

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?

Why do you use dynamic SQL? Can't you have a normal query `select * into #tmpA from TableA where create_date >= getDate - @DRange and is_enabled = 1` ? Covert @DRange into different type if needed — Yuriy Galanter, Oct 24 '13 at 01:57
Because I will need to combine the other parameter, such as database name into the query. — William Tang, Oct 24 '13 at 02:10

score 11 · Accepted Answer · edited Jul 29 '20 at 18:40

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

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;

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.

Thanks for your comment, I have use ##tmpA instead of #tmp and the problem has solved. — William Tang, Oct 24 '13 at 02:28
@WilliamTang no, it hasn't. Do you know what ##tmpA does? This creates a GLOBAL temp table. Guess what happens when two people run this stored procedure at the same time (regardless of what your date range or database parameters are). — Aaron Bertrand, Oct 24 '13 at 02:35

score 3 · Answer 2 · edited Aug 18 '17 at 06:55

3

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

edited Aug 18 '17 at 06:55

CuriousKid

605
5
24

answered Aug 18 '17 at 05:41

Sani

31
1

Using temp table with exec @sql in stored procedure

2 Answers2

Linked