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?
To use the UNION operator, you write the dividual SELECT statements and join them by the keyword UNION. The columns returned by the SELECT statements must have the same or convertible data type, size, and be the same order. The database system processes the query by executing two SELECT statements first.
Conclusion. Combining several tables to one large table is possible in all 3 ways. As we have seen, the behavior of UNION in SQL Server and UNION in DAX within Power BI is very similar. Here tables with the same number of columns are placed directly under each other.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With