MSSQL join a local table with a remoted linked server function table

Question

I would like to join a local table with a table with return by a remote link server function.

This is how I execute the remote link server function.

exec linkedServer.DB.dbo.sp_executesql N'SELECT *  FROM FunctionA(''parameter'')' 

I would like to join to local table as something like this:

select * from LocalTable a 
left join 
    (exec linkedServer.DB.dbo.sp_executesql 
          N'SELECT * FROM FunctionA(''parameter'')') as b 
on a.key = b.key

How can I do this? Do I need a temp table to do this?

Answer 1

You need to use OPENQUERY, and for making join need to add function column names to with part like ;WITH b(col1, col2, ...)

;WITH b(key) AS (
    SELECT * FROM OPENQUERY (linkedServer, N'SELECT * FROM DB.dbo.FunctionA(''parameter'')')
)
SELECT * FROM LocalTable a
    LEFT JOIN b on a.key = b.key

or you can make without with

SELECT * FROM LocalTable a
    LEFT JOIN (SELECT * FROM OPENQUERY (linkedServer, N'SELECT * FROM DB.dbo.FunctionA(''parameter'')'))b(key) 
        on a.key = b.key