How to assign data to a user-defined table type from a Select query in SQL Server 2014

Question

I have a stored procedure that retrieves three columns from multiple tables. I want to get the results in a user-defined multivalued table and pass the variable to another procedure to perform operations on these variables. However, it does not work. I have the following code. Any idea why it is not working?

--This is the initial stored procedure Create Procedure spSelectData AS BEGIN Select Userid, first_date, last_update From Users END --This is to create the table type. Create type Task1TableType AS TABLE ( Userid nvarchar(20), First_date datetime, Last_update datetime ) --Declare a table of type DECLARE @firstStep AS Task1TableType ( Userid nvarchar(20), First_date datetime, Last_update datetime ) Insert @firstStep EXEC spSelectData Select * from @firstStep -- This is the procedure 1 CREATE PROC spTest1 @TTType Task1TableType READONLY AS BEGIN Select * from @TTType END 

Answer 1

The problem is here:

 DECLARE @firstStep AS Task1TableType ( Userid nvarchar(20), First_date datetime, Last_update datetime ) Insert @firstStep EXEC spSelectData; 

Must be:

 DECLARE @firstStep AS Task1TableType; Insert INTO @firstStep EXEC spSelectData; EXEC spTest1 @firstStep; 

There is no need to define the columns where the type is given, and INSERT requires an INTO clause. After that, your code will change.

SqlFiddleDemo