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

There are various ways to handle the situation:

Table-Valued User Defined Functions (UDFs)
Views
Dynamic SQL
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.

Answer 4

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

Convert the s procs to views, if possible.
Move the logic on which proc to call to your application (if one exists). Have it run each proc separately and combine the results.

友情链接