[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Combining standard SELECT and EXEC statements with UNION ALL

Posted on 2004-10-25
12
Medium Priority
?
11,940 Views
Last Modified: 2008-01-09
(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.
0
Comment
Question by:pique_tech
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +3
12 Comments
 
LVL 34

Accepted Solution

by:
arbert earned 400 total points
ID: 12406986
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
 
LVL 12

Assisted Solution

by:kselvia
kselvia earned 200 total points
ID: 12407024
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
 
LVL 8

Assisted Solution

by:sigmacon
sigmacon earned 200 total points
ID: 12407256
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
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
LVL 12

Expert Comment

by:kselvia
ID: 12407262
I'm not really suprised. I knew it was a possibility :)  Thanks for testing it!
0
 
LVL 7

Assisted Solution

by:ala_frosty
ala_frosty earned 200 total points
ID: 12407333
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
 
LVL 34

Expert Comment

by:arbert
ID: 12407400
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
 
LVL 7

Expert Comment

by:ala_frosty
ID: 12407794
Good point arbert
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 12413908
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 12413912
NOTE: OPENROWSET will only return the FIRST result set from the sp; any others are ignored.
0
 
LVL 12

Author Comment

by:pique_tech
ID: 12414194
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
 
LVL 34

Expert Comment

by:arbert
ID: 12418283
Nice Scott....
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 12422101
Thanks arbert.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question