Join to Dynamic SQL code

The situation: 

You have a dynamic SQL code that return a select results, and you want to make it available to join other queries (you don't know what will be the queries).

Solution:

1. Insert the dynamic SQL code into stored procedure.

2. Join it using OpenRowSet:

Select *

From OpenRowSet( 'SQLNCLI',

'Server=(Local);Trusted_Connection=Yes',

'Set FmtOnly Off Exec DB_Name.dbo.SP_Name'

) d

JOIN ...

Notes:

1.You need to configure the server 'Ad Hoc Distributed Queries' property to 1:

SP_Configure 'Ad Hoc Distributed Queries',1;

Go

Reconfigure With Override;

Go

2. Stored procedure that executed from OpenRowSet can't get parameters.

3. Check performance.