Search code examples
sql-serverfunctionmultiple-selecttable-functions

Table function for each record in a query

I have a function that has two outputs ...

dbo.func1(@code) -> Table(out1, out2)

This function is too costly and takes much time to calculate these two outputs. and I have a query like this :

SELECT code, name,
(SELECT out1 dbo.func1(code)), (SELECT out2 dbo.func1(code))
FROM MyInnerJoinedTablesResult

But my costly function is call for two time but I want to call it one time for each record selected in my table... and result in two column in any row (not double rows)

SELECT code, name,
(out1 in func1), (out2 in func2)
FROM MyInnerJoinedTablesResult
Solution

  • You need to use Cross Apply

    SELECT code, name, func.out1, func.out2
FROM MyInnerJoinedTablesResult
cross apply dbo.func1(code) as func