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.

"How can we create a temporary table dynamically....... without defining temporary table." ????? — Luuk
– Luuk, Commented Dec 26, 2020 at 13:11
Does this answer your question? Create table from nth result set of stored procedure — SMor
– SMor, Commented Dec 26, 2020 at 13:24
Thanks @SMor Here in above link it suggests to use SQLCLR. I have not yet explored it much. But it surely says to code out of SQL server window. My ultimate goal is to write a tSQLt test case where i will define a temp table definition and compare the 2nd resultset of my proc for its schema. So in this case i cannot define the temp table. Otherwise test case has no meaning. — user13875967
– user13875967, Commented Dec 26, 2020 at 13:31
T-SQL does not, natively, support separatation of datasets from a Stored Procedure. Sounds like you should have 2 procedures. — Thom A
– Thom A ♦, Commented Dec 26, 2020 at 13:48
Perhaps the better question is why you (or somone in your org) decided to write a stored procedure to generate multiple resultsets. That is a problematic approach to designing/writing a "unit of work" in the first place - and obviously something that tsql is not designed to handle. — SMor
– SMor, Commented Dec 26, 2020 at 14:18

Martin Smith · Accepted Answer · 2020-12-27 14:59:12Z

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

edited Dec 27, 2020 at 14:59

answered Dec 27, 2020 at 14:50

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

SQLServerBuddy · Accepted Answer · 2020-12-26 14:13:48Z

0

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

edited Dec 26, 2020 at 14:13

answered Dec 26, 2020 at 13:49

SQLServerBuddy

1616 bronze badges

3 Comments

user13875967 Over a year ago

Actually I saw this option as well but its not enabled on our organization server due to security reasons.

SQLServerBuddy Over a year ago

Two more options suggested. But, decide yourself on limitations and performance perspective as well

user13875967 Over a year ago

But sys.dm_exec_describe_first_result_set_for_object can be used for 1st resultset only. I want to access 2nd resultset from sp1.

Collectives™ on Stack Overflow

Create a temporary table dynamically from calling an stored procedure

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related