[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 12186
  • Last Modified:

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.
0
pique_tech
Asked:
pique_tech
  • 3
  • 3
  • 2
  • +3
4 Solutions
 
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
 
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 3
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now