sqlsql-serveruser-defined-types

Assign Data to a User-Defined Table type from Select Query?


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

Solution

  • 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.

    SqlFiddleDemo