Querying against result of EXEC

user_1491884 0
Viewed 34

My EXEC:

SET @Sql = "SELECT r.Id FROM Record r where r.Price > 100"

How do I query against the result of @Sql? Ideally something like this:

SELECT * FROM Record r JOIN Patient p 
ON r.Id = p.recordId 
WHERE r.Id IN (EXEC(@Sql))
dynamic-sql tsql sql-server sql
1 Answers

Maybe this:

SET @Sql = 'SELECT r.Id FROM Record r where r.Price > 100'

CREATE TABLE #DataSource 
(
    [ID] INT
);

INSERT INTO #DataSource ([ID])
EXEC(@SQL);

SELECT * 
FROM Record r 
JOIN Patient p 
    ON r.Id = p.recordId 
WHERE r.Id IN (SELECT [ID] FROM #DataSource);
user_10803540
Related