Exec Stored Procedure into Dynamic Temp Table

Create a temporary table dynamically from calling an stored procedure

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

Can't store procedure into dynamic temp table

You can't use temporary table in such a manner.

By this code: SELECT INTO #TMP1 you're implicity creating temporary table, and it is accessible in the scope of your stored procedure - but not outside of this scope.

If you need this temporary table to be accessible outside of stored procedure, you have to remove INTO #TMP1 from stored procedure and explicitly create it outside:

create table #tmp1 (columns_definitions_here)

insert into #tmp1
exec  dbo.ProcDim N'value1', N'value2';

select * from #TMP1

Notice you have to explicitly create temporary table in this case, supplying all column names and their data types.

Alternatively you can change your stored procedure to be user-defined table function, and in this case you will be able to implicitly create and populate your temporary table:

create function dbo.FuncDim
(
    @Dim1 nvarchar(50), 
    @Dim2 nvarchar(50)
)
returns @result TABLE (columns_definition_here) 
as
begin
    ... your code
   return
end

select  *
into  #TMP1
from dbo.FuncDim(@Dim1, @Dim2)

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

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.

---

Related Topics