Create a temporary table dynamically from calling an stored procedure

Question

I want to create a temporary table from calling a stored procedure as something like below

Select * 
into #temp1 
from 
    exec sp1;

or

select * 
into #temp1 
from sys.dm_exec_describe_first_result_set_for_object(object_id('dbo.sp1'), 1) 

to get 2nd result set

I know later option is used to get first resultset only. But I want to get nth result set and create a temp table from it directly without defining temporary table.

My ultimate goal is to compare columns data type of a 2nd resultset from an SP with my expected table schema using tSQLt test case. So defining both actual table and expected table has no meaning. It will pass every time.

Answer 1

My ultimate goal is to compare columns data type of a 2nd resultset from an SP with my expected table schema using tSQLt test case.

Refactoring the code returning a second resultset into its own proc would make this more easy to test but it is do-able.

Supposing your procedure under test looks like

CREATE PROCEDURE dbo.ProcedureUnderTest
AS
BEGIN

SELECT 1 AS ResultSet1Col1

SELECT 2 AS ResultSet2Col1, 'Foo' AS ResultSet2Col2

END

You can achieve your desired goal of validating the format of the second result set by nesting a call to tSQLt.ResultSetFilter inside an execution of tSQLt.AssertResultSetsHaveSameMetaData

CREATE TABLE #expected
(
   ResultSet2Col1 INT NULL,
   ResultSet2Col2 VARCHAR(3) NULL
)


EXEC tSQLt.AssertResultSetsHaveSameMetaData
  @expectedCommand = 'SELECT * FROM #expected',
  @actualCommand = 'EXEC tSQLt.ResultSetFilter 2, ''EXEC dbo.ProcedureUnderTest'';'