Returning table from table-valued function and set that value in temp table

Question

I have a table valued function. Also I have scalar valued function within I have declared a temp table. I want to execute the table valued function inside of scalar valued function and set that value in temp table soemething like this

 **Exec @tempTable=TableValuedFunction**

How could i do this?

Here is the table valued function

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
)

How does your TVF result looks like? – John Woo Jan 19 '18 at 07:20 — John Woo, Jan 19 '18 at 07:20
Look above i have edited my post – So_oP Jan 19 '18 at 07:24 — So_oP, Jan 19 '18 at 07:24

score 7 · Accepted Answer · answered Jan 19 '18 at 07:34

First, you need to have a table variable in which the structure is the same (or depends on your desired columns) in the result of your TVF . Example:

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

Then insert the rows return from your TVF into the table variable:

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

score 2 · Answer 2 · answered Jan 19 '18 at 07:44

With this approach you need to define the table variable in advance. Table Variables also not very good for performance if the data volume is large. As per my experience you should use a Temporary table rather than a table Variable. With this approach we can also avoid table creation script.

SELECT * INTO #TempVariable FROM [dbo].[fn_Functiont]()

Returning table from table-valued function and set that value in temp table

2 Answers2