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 — Comfortably Numb
– Comfortably Numb, Commented Oct 24, 2013 at 1:57
Because I will need to combine the other parameter, such as database name into the query. — William Tang
– William Tang, Commented Oct 24, 2013 at 2:10

Community · Accepted Answer · 2020-07-29 18:40:42Z

11

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

edited Jul 29, 2020 at 18:40

CommunityBot

11 silver badge

answered Oct 24, 2013 at 2:02

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

William Tang Over a year ago

Thanks for your comment, I have use ##tmpA instead of #tmp and the problem has solved.

Aaron Bertrand Over a year ago

@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).

CuriousKid · Accepted Answer · 2017-08-18 06:55:04Z

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, 2017 at 6:55

CuriousKid

6055 silver badges24 bronze badges

answered Aug 18, 2017 at 5:41

Sani

311 bronze badge

Collectives™ on Stack Overflow

Using temp table with exec @sql in stored procedure

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related