I have a stored procedure which retrieves three columns from multiple tables. I want to get the results in a user defined multi-valued table and pass the variable to another procedure to perform operations on the variable data.
However it is not working. Any idea why it this 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
Advertisement
Answer
The problem is here:
DECLARE @firstStep AS Task1TableType ( Userid nvarchar(20), First_date datetime, Last_update datetime ) Insert @firstStep EXEC spSelectData;
Should be:
DECLARE @firstStep AS Task1TableType; Insert INTO @firstStep EXEC spSelectData; EXEC spTest1 @firstStep;
There is no need to defining columns where type is defined, and INSERT
require INTO
clause. After that change your code works.