CODEWITHSUNDEEP
sqlsql-servert-sqlstored-proceduresstored-functions
So_oP
So_oP

Reputation: 1293

Accessing table returning by table-valued function from SP

I have 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
)

And SP. In that SP i have declared temporary table like this 

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

after declaring i just inserting info 

INSERT INTO @tempTable
SELECT * FROM [dbo].[fn_Functiont]()
Select @column1,colum2...,from @tempTable

The problem is that i have to declare a lot of columns in @temptable and code looks like ugly.So is there a better way to reading rows in SP from table valued function?

Upvotes: 1

Views: 59

Answers (1)

Jayasurya Satheesh
Jayasurya Satheesh

Reputation: 8043

Instead of table Variable @tempTable Use Temp Table and Try This

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

Upvotes: 1

Related Questions