Execute Sp_Executesql for Select...Into #Table But Can't Select Out Temp Table Data

Execute sp_executeSql for select...into #table but Can't Select out Temp Table Data

Local temporary table #table_name is visible in current session only, global temporary ##table_name tables are visible in all sessions. Both lives until their session is closed.
sp_executesql - creates its own session (maybe word "scope" would be better) so that's why it happens.

INSERT INTO temporary table from sp_executsql

You can't. There is simply no way to create a #temptable from an EXEC output schema.

INSERT ... EXEC requires the table to exists (thus must know the schema before execution).

SELECT ... INTO does not support EXEC as a source.

How can I store a temporary table from an executed procedure?

You can store the data in Global temporary table (##) with Select * into approach and to store in #temp table you have to create the table first which I am aware of while using dynamic sql But you can certainly do that in run time but still you may need some physical table to access it.

create table testtmp (id int, namen varchar(15)) 

--inserting the data into physical table 
insert into testtmp (id, namen)

select 1 as ID, 'XYZ' as namen union all
select 2 as ID, 'ABC' as namen union all
select 3 as ID, 'DIG' as namen

create table #temp (ID int) 

declare @sql nvarchar(max) = 'select ID from testtmp' 
insert into #temp exec sp_executesql @sql 

select * from #temp 

Gives you this output: 

 ID 
  1 
  2 
  3 

With global temporary table you can do it easily and you don't have to create any tables, you can specify column names if you would like to.

 declare @sql nvarchar(max) = 'select * into ##Gloabltmptest from testtmp' 
 exec sp_executesql @sql 

 select * from ##Gloabltmptest 

Output:

 ID  namen
 1  XYZ
 2  ABC
 3  DIG

Added table variable as well, similar to #temp tables.

declare @table table (IDtab int, nametab varchar(15)) 

declare @sql nvarchar(max) = 'select * from testtmp' 
insert into @table exec sp_executesql @sql 

select * from @table 

Using temp table with exec @sql in stored procedure

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

Dynamic SQL results into temp table in SQL Stored procedure

Try:

SELECT into #T1 execute ('execute ' + @SQLString )

And this smells real bad like an sql injection vulnerability.

---

correction (per @CarpeDiem's comment):

INSERT into #T1 execute ('execute ' + @SQLString )

also, omit the 'execute' if the sql string is something other than a procedure

---

Related Topics