如何从SQL Server 2014中的“选择查询”将数据分配给用户定义的表类型

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

1 个解决方案

#1

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.

无需定义定义类型的列,INSERT需要INTO子句。在那之后你的代码就可以了。

SqlFiddleDemo

#1