pique_tech
asked on
Combining standard SELECT and EXEC statements with UNION ALL
(This is tangentially related to https://www.experts-exchange.com/questions/21182188/Call-SQL-UDF-on-Linked-Server.html)
Can anyone confirm whether it's possible to combine "standard" SELECT statements and EXECUTE statements that return records into one result set using UNION ALL, i.e.,
SELECT blah1, blah2, blah3
FROM table1
UNION ALL
SELECT blah1a, blah2a, blah3a
FROM table2
...
UNION ALL
EXEC SomeValidStoredProc --which returns records like blahX, blahY, blahZ
...
Of course, I've already validated that
1. my SELECTs and EXECUTEs are all column-wise datatype compatible and
2. all return results when run individually
I'm building a pretty long SELECT ... FOR XML EXPLICIT so the format is pretty strictly defined. As usual, thanks in advance.
Can anyone confirm whether it's possible to combine "standard" SELECT statements and EXECUTE statements that return records into one result set using UNION ALL, i.e.,
SELECT blah1, blah2, blah3
FROM table1
UNION ALL
SELECT blah1a, blah2a, blah3a
FROM table2
...
UNION ALL
EXEC SomeValidStoredProc --which returns records like blahX, blahY, blahZ
...
Of course, I've already validated that
1. my SELECTs and EXECUTEs are all column-wise datatype compatible and
2. all return results when run individually
I'm building a pretty long SELECT ... FOR XML EXPLICIT so the format is pretty strictly defined. As usual, thanks in advance.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Ken Selvia
I'm not really suprised. I knew it was a possibility :) Thanks for testing it!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Why move the records twice and double the work --just union from the first:
Create #tblTemp (blah1 int, blah2 bigint, blah3 bit)
INSERT INTO #tblTemp
EXEC SomeValidStoredProc
SELECT blah1, blah2, blah3
FROM table1
UNION
SELECT blah1a, blah2a, blah3a
FROM table2
UNION
SELECT blah1a, blah2a, blah3a
FROM table3
union
select blah1a, blah2a, blah3a
from #tblTemp
Create #tblTemp (blah1 int, blah2 bigint, blah3 bit)
INSERT INTO #tblTemp
EXEC SomeValidStoredProc
SELECT blah1, blah2, blah3
FROM table1
UNION
SELECT blah1a, blah2a, blah3a
FROM table2
UNION
SELECT blah1a, blah2a, blah3a
FROM table3
union
select blah1a, blah2a, blah3a
from #tblTemp
Good point arbert
You don't need a temp table at all if you are willing to set up a native SQL login and userid to exec the sp. For example:
SELECT *
FROM someTable
UNION ALL
SELECT ors.*
FROM OPENROWSET('SQLOLEDB','SQL
'EXEC someValidStoredProc') ors
Naturally the login/userid must have authority to exec the sp.
SELECT *
FROM someTable
UNION ALL
SELECT ors.*
FROM OPENROWSET('SQLOLEDB','SQL
'EXEC someValidStoredProc') ors
Naturally the login/userid must have authority to exec the sp.
NOTE: OPENROWSET will only return the FIRST result set from the sp; any others are ignored.
ASKER
Thanks to all for the insight and feedback. I opted to take another path altogether: I moved my function from a linked server to the local server but calling data from the linked server--ended up being much cleaner than trying to call the function and providing the parameter from the linked server.
Nice Scott....
Thanks arbert.