CODEWITHSUNDEEP
sqlsql-servertsqlt
user13875967
user13875967

Reputation: 21

Create a temporary table dynamically from calling an stored procedure

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.

Upvotes: 1

Views: 1547

Answers (2)

Martin Smith
Martin Smith

Reputation: 453278

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'';'

Upvotes: 1

SQLServerBuddy
SQLServerBuddy

Reputation: 161

Try this way

SELECT * INTO #temp1 FROM OPENROWSET('SQLNCLI','Server=YourServer;Trusted_Connection=yes;','exec DBName.Schema.sp1') AS a

The below one is limited scope, Since the "#Temp1" can't be used outside of it!

Declare @Table VARCHAR(MAX)=''
select @Table = @Table + ',' + [Name] + ' ' + system_type_name  from sys.dm_exec_describe_first_result_set_for_object(object_id('dbo.sp1'), 1) 
set @Table = stuff(@Table,1,1,'')
Exec('Create Table #temp1(' + @Table + ')
Insert #temp1
Exec dbo.sp1
')

Or

The below one should work with "Global Temp table"

Declare @Table VARCHAR(MAX)=''
select @Table = @Table + ',' + [Name] + ' ' + system_type_name  from sys.dm_exec_describe_first_result_set_for_object(object_id('dbo.sp1'), 1) 
set @Table = stuff(@Table,1,1,'')
Exec('Create Table ##temp1(' + @Table + ')')

Insert ##temp1
Exec dbo.sp1

drop table ##temp1

Upvotes: 0

Related Questions