Combining Stored Procedures Result Sets

Question

I am executing multiple stored procedures which return results, but I want it to look like one result set. I tried UNION ALL but that only works with SELECT statements.

EXEC TaskSummary @TaskName = "Demo", @Days ="5", @GroupName = "Charlie"

EXEC TaskSummary @TaskName = "Framing", @Days="15", @GroupName = "Charlie"

EXEC TaskSummary @TaskName = "Electrical Rough-In", @Days= 7, @GroupName = "Foxtrot"

EXEC TaskSummary @TaskName = "Insulation", @Days = 2, @GroupName = "Charlie"

EXEC TaskSummary @TaskName = "Exterior Doors", @Days= 2, @GroupName = "Charlie"

EXEC TaskSummary @TaskName = "Install Windows", @Days= 2, @GroupName= "Charlie"

EXEC TaskSummary @TaskName = "Bathroom Tiles", @Days= 6, @GroupName = "Charlie"

EXEC TaskSummary @TaskName = "Prime Walls" , @Days= "2", @GroupName = "Bravo"

EXEC TaskSummary @TaskName = "Painting Interior" , @Days= "3" , @GroupName = "Charlie"

EXEC TaskSummary @TaskName = "Mill Work", @Days="3", @GroupName= "Charlie"

EXEC TaskSummary @TaskName = "Backsplash Install" , @Days= "1" , @GroupName= "Charlie"

EXEC TaskSummary @TaskName = "Electrical Dress-Up" , @Days="1" , @GroupName= "Foxtrot"

EXEC TaskSummary @TaskName = "Interior Hardware Install", @Days= "2" , @GroupName= "Charlie"

EXEC TaskSummary @TaskName = "Final Touch Up", @Days= "2", @GroupName = "Bravo"

EXEC TaskSummary @TaskName = "Punch List", @Days= "2", @GroupName= "Charlie"

can you also post the query inside the TaskSummary Stored Procedure — JC Borlagdan, Jan 22 '19 at 23:07

score 2 · Answer 1 · answered Jan 22 '19 at 23:09

2

You can create one temp table and insert all stored procedure result within it, then perform a select query:

--CREATE TEMP TABLE
CREATE TABLE #TBL(Column1 ....)

--Eexute all stored procedures with an insert statment
INSERT INTO #TBL
EXEC TaskSummary @TaskName = "Demo", @Days ="5", @GroupName = "Charlie"

INSERT INTO #TBL
EXEC TaskSummary @TaskName = "Framing", @Days="15", @GroupName = "Charlie"

...

--Select data from temp table
SELECT * FROM #TBL

answered Jan 22 '19 at 23:09

Hadi

36,233
13
65
124

1

Voted this answer as you should use a temp table `#FOO` instead of a table variable `@FOO`, especially for larger data collections like this could create. – Adrian J. Moreno Jan 22 '19 at 23:22

score 1 · Answer 2 · answered Jan 22 '19 at 23:09

You can use a table variable to collect all the results.

DECLARE @u TABLE
           (<columns as the result of TaskSummary regarding number, order and type>);
INSERT INTO @u
            EXECUTE TaskSummary <parameters>;
...
INSERT INTO @u
            EXECUTE TaskSummary <other parameters>;
SELECT *
       FROM @u;

Combining Stored Procedures Result Sets

2 Answers2