1

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.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
user13875967
  • 21
  • 6
  • 1
    "How can we create a temporary table dynamically....... without defining temporary table." ????? – Luuk Dec 26 '20 at 13:11
  • 1
    Does this answer your question? [Create table from nth result set of stored procedure](https://stackoverflow.com/questions/26262611/create-table-from-nth-result-set-of-stored-procedure) – SMor Dec 26 '20 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 Dec 26 '20 at 13:31
  • 2
    T-SQL does not, natively, support separatation of datasets from a Stored Procedure. Sounds like you should have 2 procedures. – Thom A Dec 26 '20 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 Dec 26 '20 at 14:18
  • Yeah I understand @SMor but the thing is that sp is designed very long back this way. and most of the sps in application are returning multiple resultsets. So here i want to understand how to compare schema of a particular resultset with my expected table schema? I am actually new to tSQLt. or is it possible any other way than the one i am trying to do? – user13875967 Dec 26 '20 at 14:26
  • Can you migrate your stored procedure to a table-valued functions? – John Cappelletti Dec 26 '20 at 14:44
  • Short answer is no - you have been given alternatives. Start coding or change your goal / implementation / usage of tsqlt – SMor Dec 26 '20 at 16:44

2 Answers2

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'';'
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
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
SQLServerBuddy
  • 161
  • 6