Solved

Combining standard SELECT and EXEC statements with UNION ALL

Posted on 2004-10-25
11,346 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
Question by:pique_tech
    12 Comments
     
    LVL 34

    Accepted Solution

    by:
    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
    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
    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
     
    LVL 12

    Expert Comment

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

    Assisted Solution

    by:ala_frosty
    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
    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
    Good point arbert
    0
     
    LVL 68

    Expert Comment

    by:ScottPletcher
    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 68

    Expert Comment

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

    Author Comment

    by:pique_tech
    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
    Nice Scott....
    0
     
    LVL 68

    Expert Comment

    by:ScottPletcher
    Thanks arbert.
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Suggested Solutions

    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

    884 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now