sql - 从表值函数返回表并在临时表中设置该值

Question

我有一个表值函数。另外，我在其中声明了一个临时表，其中有标量值函数。我想在标量值函数内部执行表值函数，并在临时表中设置该值，如下所示

 **Exec @tempTable=TableValuedFunction**

我怎样才能做到这一点？

这是表值函数

ALTER FUNCTION [dbo].[fn_Functiont]()
RETURNS TABLE 
AS
RETURN 
(   
SELECT d.*, b.Name AS Name, ps.Name AS PaymentSystemName, c.UserName AS UserName, c.FirstName AS ClientFirstName, c.LastName AS LastName, c.Number AS DocumentNumber, c.Id
FROM Document AS d
JOIN System AS ps ON d.SystemId = ps.Id
JOIN Client AS c ON c.Id = d.ClientId
LEFT JOIN Shop AS b ON b.Id = d.ShopId
WHERE d.OperationTypeId IN (2, 4, 5) AND c.Type = 1
)

Answer 1

首先，您需要一个表变量，其中的结构与 TVF 结果中的结构相同(或取决于您所需的列)。示例:

DECLARE @tempTable AS TABLE 
(
   .. columns here ...
)

然后将从 TVF 返回的行插入到表变量中:

INSERT INTO @tempTable
SELECT * FROM [dbo].[fn_Functiont]()