Combining standard SELECT and EXEC statements with UNION ALL

(This is tangentially related to http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21182188.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.
LVL 12
pique_techAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arbertCommented:
No, you would have to perform the exec into a temporary table first and then union the results.  Either that, or possible use a UDF.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ken SelviaRetiredCommented:
In a sense it is possible if the exec returns the same columns as the selects you can create a new procedure

create procedure p_select_and exec as

begin

SELECT blah1, blah2, blah3
FROM table1
UNION ALL
SELECT blah1a, blah2a, blah3a
FROM table2

EXEC SomeValidStoredProc   --which returns records like blahX, blahY, blahZ

end

exec p_select_and_exec will return a single result with the SELECTS combined with the exec.

(At least I believe it will but I don't think I have ever actually done it.)

0
sigmaconCommented:
kselvia, that approach sounded interesting, so I tried it:

create proc p1
as
    select *
    from h
    where [left] between 11 and 26
go

create proc p2
as
    select *
    from h
    where [left] > 25

    execute p1
go


I got two result sets. Maybe I am doing something wrong
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Ken SelviaRetiredCommented:
I'm not really suprised. I knew it was a possibility :)  Thanks for testing it!
0
ala_frostyCommented:
Writing out what I first thought of and Arbert stated:

Create #tblTemp (blah1 int, blah2 bigint, blah3 bit)

INSERT INTO #tblTemp
  EXEC SomeValidStoredProc

INSERT INTO #tblTemp
FROM (
SELECT blah1, blah2, blah3
FROM table1
UNION
SELECT blah1a, blah2a, blah3a
FROM table2
UNION
SELECT blah1a, blah2a, blah3a
FROM table3) As AllTheBlahs

Select *
FROM #tblTemp
0
arbertCommented:
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
0
ala_frostyCommented:
Good point arbert
0
Scott PletcherSenior DBACommented:
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','SQLServerName';'UserId';'password',
   'EXEC someValidStoredProc') ors


Naturally the login/userid must have authority to exec the sp.
0
Scott PletcherSenior DBACommented:
NOTE: OPENROWSET will only return the FIRST result set from the sp; any others are ignored.
0
pique_techAuthor Commented:
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.
0
arbertCommented:
Nice Scott....
0
Scott PletcherSenior DBACommented:
Thanks arbert.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.