Using UNION with Multiple Stored Procedures

Question

Post reply

Using UNION with Multiple Stored Procedures

dean gross

SSCrazy

Points: 2632
More actions
January 30, 2010 at 10:50 am

#215144

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

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

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

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 1

Hi

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

Greets

Flo

Paul White SSC Guru Points: 150467 More actions · Answer 2

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

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 3

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: 150467 More actions · Answer 4

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

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi