Dynamic SQL results into temp table in SQL Stored procedure

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?

From stackoverflow

• Not sure if I understand well, but maybe you could form the CREATE statement inside a string, then execute that String? That way you could add as many columns as you want.

  Dhana : but, i don't know columns, it is dynamic.

• Try:

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

  And this smells real bad like an sql injection vulnerability.

  ercan : Tough accepted answer, I cannot make this work! First it complains that there is no * after SELECT. And when I put it, it complains that there is no table to select from.. SELECT * INTO #tmp_input EXECUTE('SELECT 1 AS test') ; >>> SQL Server Database Error: Must specify table to select from. On SQL Server 2005!

• 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

• Jesse's answer Really solved the problem ..Thanks