TSQL: union results from two selects (procedures?)

Question

I have two procedures - two huge sets of selects with several sub-select and unions. I need to union results from these procedures and I still need them to exists separately.

Something like that:

if @Param = 1 Then 
    PROCEDURE1
if @Param = 2 THEN 
    PROCEDURE2
if @Param = 3 Then
    PROCEDURE1 union PROCEDURE2

I read that it's impossible to have union on procedures and I can't use temporary tables.

Any idea?

Answer 1

You can convert the procedures to views.

OR

You can exec the procedures into a temp table, and then exec the other one into the same temp table:

create table #sometable (table definition here)

if @Param = 1 or @Param = 3 begin
    insert #sometable exec PROCEDURE1
end

if @Param = 2 or @Param = 3 begin
    insert #sometable exec PROCEDURE2
end

select * from #sometable

Answer 2

You could either:

1) Insert the data into a temp table then SELECT from that:

--Define #t here, with correct schema to match results returned by each sproc
INSERT #t EXECUTE PROC1
INSERT #t EXECUTE PROC2
SELECT * FROM #t

2) Just return 2 resultsets from the sproc and let your calling code handle 2 resultsets

Answer 3

Without the use of temp tables there are only two other ways that I can think of.

1. Convert the s'procs to views, if possible.

2. Move the logic on which proc to call to your application (if one exists). Have it run each proc separately and combine the results.

Answer 4

There are various ways to handle the situation:

1. Table-Valued User Defined Functions (UDFs)
2. Views
3. Dynamic SQL
4. Temp tables

The question lacks concrete details so it's hard to recommend one over another, much less provide a tailored answer. It's plausible that the logic could be performed within a single query.