Solved

Simple SQL Union Problem

Posted on 2003-12-11
7
1,124 Views
Last Modified: 2007-12-19
Hi, I am new at SQL.  What is wrong with my syntax?

Select *
From "STOREDPROCEDURE1"
UNION
Select *
From "STOREDPROCEDURE2"
ORDER BY ChangeNoNOTVAL;
0
Comment
Question by:eawucker
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9925195
Why can you not modify the stored proc so that union operation is performed there.


Try this. But result sets from proc1 and proc 2 should return a resultset so that the datatypes match.

create table #temp (
col1 int,
col2 int,
etc...
)

insert #temp
exec STOREDPROCEDURE1

insert #temp
exec STOREDPROCEDURE1

select *from #temp
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9925199
Cannot select from Stored Procedure.

create table #temp (
col1 int,
col2 int,
etc...
)

insert #temp
exec STOREDPROCEDURE1

insert #temp
exec STOREDPROCEDURE2

select *from #temp

 
 
 
0
 

Author Comment

by:eawucker
ID: 9925212
I think I explained wrong.  I want to create a stored procedure that unions 2 other stored procedures.  I can't get the syntax right.  What is it?
0
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
LVL 2

Expert Comment

by:curlypinhead
ID: 9925249
Why not create a new Stored Procedure and just union the queries in the 2 other stored procedures?

Example:

Sproc1:
select * from table1

Sproc2:
select * from table2

Sproc3 (new sproc):
select * from table1
union
select * from table2


I've never seen a union of stored procedures.  That doesn't mean it is impossible though.

curlypinhead
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9925426
To exeute both procs in one proc do the following:

Create Procedure sp_Main
As
Begin
create table #temp (
col1 int,
col2 int,
etc...
)

insert #temp
exec STOREDPROCEDURE1

insert #temp
exec STOREDPROCEDURE2

select *from #temp -- returns single result set

End

If you need 2 result sets then try:

Create Procedure sp_Main
As
Begin

exec STOREDPROCEDURE1 -- one result set

exec STOREDPROCEDURE2 -- second result set

End

HTH

0
 

Accepted Solution

by:
rsrsm earned 75 total points
ID: 9925625
One of the possible way is:
1st stored procedure:
create proc prc1 as
select col1 from tbl1

2nd procedure:
create proc prc2 as
select col2 from tbl2

3rd combined or union would be
create proc prc 3 as
begin
exec prc1
exec prc2
end

One draw back is tht result set displayed in 2 different grids and not next to each other.

RSRSM
0
 

Expert Comment

by:rsrsm
ID: 9925631
sorry forgot to add :
 execute the 3rd procedure run the command
exec prc3

RSRSM
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question