• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1130
  • Last Modified:

Simple SQL Union Problem

Hi, I am new at SQL.  What is wrong with my syntax?

Select *
From "STOREDPROCEDURE1"
UNION
Select *
From "STOREDPROCEDURE2"
ORDER BY ChangeNoNOTVAL;
0
eawucker
Asked:
eawucker
1 Solution
 
namasi_navaretnamCommented:
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
 
namasi_navaretnamCommented:
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
 
eawuckerAuthor Commented:
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
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
curlypinheadCommented:
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
 
namasi_navaretnamCommented:
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
 
rsrsmCommented:
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
 
rsrsmCommented:
sorry forgot to add :
 execute the 3rd procedure run the command
exec prc3

RSRSM
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now