How to store results of a Dynamic Query in a temp table without creating a table?

Question

We are writing a stored procedure responsible for getting a stored procedure name and returning a result containing the stored procedure columns and their data types. However, we bumped into a problem executing a dynamic query to return the results of stored procedure, but we can't store it in a temp table!

You can see our query below:

  DECLARE @ProcName VARCHAR(100)='spGetOraganizationsList',
  @ParamName VARCHAR(100),@DataType VARCHAR(20),
  @Query NVARCHAR(MAX)='EXEC '+'spGetOraganizationsList '

  SELECT  PARAMETER_NAME,DATA_TYPE 
  INTO #Tmp
  FROM  information_schema.PARAMETERS
  WHERE SPECIFIC_NAME=@ProcName

  DECLARE ParamCursor CURSOR 
  FOR SELECT * FROM #Tmp
  OPEN ParamCursor
  FETCH NEXT FROM ParamCursor
  INTO @ParamName,@DataType

  WHILE @@FETCH_STATUS = 0 
  BEGIN
  SET @Query=@Query+@ParamName+'=Null,'
  FETCH NEXT FROM ParamCursor INTO @ParamName,@DataType
  END
  CLOSE ParamCursor
  DEALLOCATE ParamCursor
  DROP TABLE #Tmp
  
  EXEC sp_executesql @Query

The thing is I can't store the results of it in a temp table, and OPENROWSET does not accept variables.

Answer 1

I think it comes from sql concept that it doesn't trust in result of stored procedures and because of that we cannot select on it or store it in a table by 'making in query table' method. Unless you create a table and define it's columns and sql trust to you and you insert result of it into this table for example take below situation

Create table test (name varchar(10),family varchar(20))


Insert into test
Exec sp-testResult

Now if you define wrong column for your table you will receive query runtime error .actually sql doesn't predict result of sp and leaves it to you to define result of your stored procedure.