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?

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

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