CODEWITHSUNDEEP
sqlsql-servert-sqlsql-function
So_oP
So_oP

Reputation: 1293

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

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
)

Upvotes: 6

Views: 6729

Answers (2)

Pawan Kumar
Pawan Kumar

Reputation: 2021

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]()

Upvotes: 2

John Woo
John Woo

Reputation: 263893

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]()

Upvotes: 8

Related Questions