Dynamic SQL results into temp table in SQL Stored procedure

Question

The code is as follows:

ALTER PROCEDURE dbo.pdpd_DynamicCall  @SQLString varchar(4096) = null  AS  Begin      create TABLE #T1 ( column_1 varchar(10) , column_2 varchar(100) )      insert into #T1          execute ('execute ' + @SQLString )      select * from #T1   End 

The problem is that I want to call different procedures that can give back different columns. Therefore I would have to define the table #T1 generically. But I don't know how.

Can anyone help me on this problem?

Answer 1

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

Answer 2

You can define a table dynamically just as you are inserting into it dynamically, but the problem is with the scope of temp tables. For example, this code:

DECLARE @sql varchar(max) SET @sql = 'CREATE TABLE #T1 (Col1 varchar(20))' EXEC(@sql) INSERT INTO #T1 (Col1) VALUES ('This will not work.') SELECT * FROM #T1 

will return with the error "Invalid object name '#T1'." This is because the temp table #T1 is created at a "lower level" than the block of executing code. In order to fix, use a global temp table:

DECLARE @sql varchar(max) SET @sql = 'CREATE TABLE ##T1 (Col1 varchar(20))' EXEC(@sql) INSERT INTO ##T1 (Col1) VALUES ('This will work.') SELECT * FROM ##T1 

Hope this helps, Jesse