Use results of an `exec` query as a join or "in" statement?

Question

I have a query I build into a NVARCHAR due to an issue between SQL Server and a linked Oracle server forcing me to use OpenQuery. It works fine and I get the results I need when I run exec (@OPENQUERYFULL).

My results are a single column of numbers I need for another query. I would like to be able to use those results as an "IN ()" statement or as a JOIN but what I have tried so far has failed.

Is there a way to use the results of an exec query directly in another query?

UPDATE: To be clear I was trying to avoid using a temp table.

Have you tried declaring a table variable and storing the results of the oracle query in that? A column of numbers is just a column of numbers. Can you provide an example of code which has failed? — user1666620, Jan 17 '20 at 15:42
You could use a linked server/ 4-part name to write a single query using normal JOINs, but it might be incredibly slow if there's a lot in your original OPENQUERY. You could create a temporary table (or table variable) to store the results from your OPENQUERY, and then use this in a second query, but now you have two queries and potential housekeeping. — Richard Hansell, Jan 17 '20 at 15:43
@RichardHansell I know I can write a temp table and use that. I was hoping to avoid writing a temp table and just used it like a sub query. I cannot really provide and example as it is sensitive company info and it would take a good 30 min to clean it up for a post. I was hoping someone knew of a simple way to use the results of an `exec` query as a subquery or join. — Mike - SMT, Jan 17 '20 at 15:47

user1666620 · Answer 1 · 2020-01-17T15:48:14.660

1

Declare a table variable and insert the results of the other SP into that variable:

declare @results table (
    numbers int
);

GO

insert into @results(numbers)
exec otherSP

Then you perform your join on the table variable.

edited Jan 17 '20 at 15:48

answered Jan 17 '20 at 15:46

user1666620

4,800
18
27

I was wanting to avoid doing this. I did not want to use a temp table. I already know how to do this as a temp. I really just wanted to use the results like a sub-query for a single query to run. – Mike - SMT Jan 17 '20 at 15:48
@Mike-SMT my googling indicates that you can't, other than actually creating a subquery which does the same thing as your oracle stored procedure. On the face of it, it seems like an odd requirement. – user1666620 Jan 17 '20 at 15:50
@Mike-SMT also, to be quite clear, a table variable is not a temp table. They might be used in similar ways. but in the background they're actually fairly different with different performance characteristics. – user1666620 Jan 17 '20 at 15:52
Its not really requirement. I have around 50 queries to convert and I am in the process of reducing them as these old queries were not very efficient. In an effort to reduce the amount of queries sent to the server I am trying to reduce as much as possible. If I have to use a temp table in this situation then I will. I was just hoping for a clear solution to use an `exec` in a join or in statement. – Mike - SMT Jan 17 '20 at 15:53
Oh I just realized what you are doing is slightly different then how a temp table is set up. Is there any performance difference between a "table variable" and a "temp table"? – Mike - SMT Jan 17 '20 at 15:55
@Mike-SMT could you try using a View instead of a stored procedure? – user1666620 Jan 17 '20 at 15:55
No unfortunately we cannot use a view here. I do not have the ability to set up a view. There are some constraints I have to work with as I am not the DBA. – Mike - SMT Jan 17 '20 at 15:56
1

@Mike-SMT I don't have documentation to hand, but this is a quick SO link comparing the difference between temp tables and table variables: https://stackoverflow.com/questions/11857789/when-should-i-use-a-table-variable-vs-temporary-table-in-sql-server The temp table is written to disk, whereas the table variable is stored in memory (but may be pushed to the disk) – user1666620 Jan 17 '20 at 15:59

David Browne - Microsoft · Answer 2 · 2020-01-17T16:09:51.967

1

You can send the linked server query with OPENQUERY instead of 'exec' by building a dynamic query with OPENQUERY and a join. It just takes some annoying string escaping, eg:

declare @oracleSQL nvarchar(max) = 'select 1 a, ''hello'' b from dual';

declare @sql nvarchar(max) = concat(
N'
with q as
(
  SELECT * FROM OPENQUERY (OracleSvr, N''', replace(@oracleSQL,'''','''''') ,N''')
)
select *
from q
cross join sys.objects o
')

print @sql 
exec ( @sql )

But as you can see the resulting code is a bit complicated. So I would almost always just use a temp table.

edited Jan 17 '20 at 16:09

answered Jan 17 '20 at 15:49

David Browne - Microsoft

80,331
6
39
67

Well if temp tables are my only option for the results of an exec then that's what I will do. I was trying to avoid this and use a single query. – Mike - SMT Jan 17 '20 at 15:50
Sorry I didn't realize you were not setting up a temp table. This is new to me I will have to see if I can make it work. I am not familiar with `N''', replace(@oracleSQL,'''','''''') ,N'''`. I have not used OPENQUERY in this way before it may be a bit over my head for now till I can do some research. – Mike - SMT Jan 17 '20 at 15:57

Use results of an `exec` query as a join or "in" statement?

2 Answers2