eawucker
asked on
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;
Select *
From "STOREDPROCEDURE1"
UNION
Select *
From "STOREDPROCEDURE2"
ORDER BY ChangeNoNOTVAL;
Cannot select from Stored Procedure.
create table #temp (
col1 int,
col2 int,
etc...
)
insert #temp
exec STOREDPROCEDURE1
insert #temp
exec STOREDPROCEDURE2
select *from #temp
create table #temp (
col1 int,
col2 int,
etc...
)
insert #temp
exec STOREDPROCEDURE1
insert #temp
exec STOREDPROCEDURE2
select *from #temp
ASKER
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?
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
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
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
sorry forgot to add :
execute the 3rd procedure run the command
exec prc3
RSRSM
execute the 3rd procedure run the command
exec prc3
RSRSM
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