Using UNION with Multiple Stored Procedures

  • dean gross

    SSCrazy

    Points: 2632

    I need to combine the results from multiple executions of a stored procedure. I am trying to use the Union statement as follows, but I'm getting this error message: Incorrect syntax near the keyword 'UNION". What am I doing wrong?

    Thanks

    Dean

    @StartDate DateTime = 20100101,

    @EndDate DateTime = 20100115

    AS

    BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;

    --Get Direct Labor Hours

    EXEC dbo.uspLaborHoursByCategory 'PARFLS',@StartDate, @EndDate, 100000, 799999, '100', '899','Direct Labor',6,0

    UNION

    EXEC dbo.uspLaborHoursByCategory 'CANFLS',@StartDate, @EndDate, 100000, 799999, '100', '899','Direct Labor',6,0

  • Florian Reischl

    SSC-Dedicated

    Points: 37299

    Hi

    You can use a temp table to catch both results in separate executions.

    Greets

    Flo

  • Paul White

    SSC Guru

    Points: 150442

    In case the syntax is new to you, Flo is referring to INSERT...EXEC:

    INSERT {table-name}

    EXECUTE {stored-procedure-name}

    You must create the table (normal, temporary, or variable) beforehand, and it must have a structure that matches the output from the procedure. By running the statement twice, you will end up with both sets of output from the procedure calls in the one table.

    This implements a UNION ALL type of arrangement. If you truly need UNION (which removes duplicates) rather than UNION ALL (which just does a simple concatenation), you will need to do an appropriate DISTINCT or GROUP BY on the data held in the table.

    Paul

  • Florian Reischl

    SSC-Dedicated

    Points: 37299

    Hey Paul

    Thanks for the way better description. 🙂

    @dean

    One additional information. There is one more restriction with INSERT INTO ... EXECUTE. It cannot be used recursive. Means, if your procedure internally uses the same syntax, you get an error.

    There another (crazy?) option to combine both procedure results with UNION or UNION ALL. You can use OPENROWSET as a self-link:

    SET NOCOUNT ON;

    GO

    USE tempdb;

    GO

    ---==================================================================

    -- create a sample procedure

    IF (OBJECT_ID('GetTestData') IS NULL)

    EXECUTE ('CREATE PROCEDURE GetTestData AS SELECT 1;');

    GO

    ALTER PROCEDURE GetTestData

    AS

    SELECT TOP(10) * FROM master.sys.all_columns

    GO

    ---==================================================================

    -- execute UNION ALL

    SELECT *

    FROM OPENROWSET(

    'SQLNCLI'

    ,'Server=.;Trusted_Connection=yes;'

    ,'EXECUTE tempdb.dbo.GetTestData;')

    UNION ALL

    SELECT *

    FROM OPENROWSET(

    'SQLNCLI'

    ,'Server=.;Trusted_Connection=yes;'

    ,'EXECUTE tempdb.dbo.GetTestData;')

    GO

    Greets

    Flo

    Edit: Added code comment

  • Paul White

    SSC Guru

    Points: 150442

    Hey Flo,

    Good to see you around on here again.

    Excellent point about the limitation of INSERT...EXEC - the inability to nest calls is the main reason I try to avoid this construct wherever possible. The OPENROWSET solution is indeed crazy - it's a dreadful hack really and not at all recommended. An interesting quirk though 🙂

    In case the original questioner might benefit from it, here's a link to Erland Sommarskog's terrific article about passing virtual tables around: http://www.sommarskog.se/share_data.html

    Paul

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply