stored procedure with two result sets

[joxa83]

Is there possible two fill table with second result set returned by stored proc.For instance
create proc test
as
begin
select *from orders

select *from Employees
end

insert into sometable exec test

So I want to fill table 'sometable' with result set
'select *from Employees'. There by tables 'sometable' and 'Employees' have same shema

 

[SQLDenis]

use a union

create proc test
as
begin
select *from orders
union all
select *from Employees
end

insert into sometable exec test

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[joxa83]

that is not solution.orders table and Employees
tabel do not have same shema.Besides all that how do you intend to substract data from Employees table using result of stored proc?????

 

[SQLDenis]

Okay I see what you mean, I understood that employees and orders have the same schema

Why do you need to return 2 result sets froma stored procedure? can't you make 2 procs?

What is it that you are trying to accomplish?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[joxa83]

that is the problem.i can't make two procedures I need to use existing proc wich returns two sets.Then I need to insert second set into some temp table to make some calculations.It means I can't use front end apps or or create two procs.

 

[SQLDenis]

if you want to insert into sometable all employees that don't have orders you would do something like this

insert sometable
select e.* from employees e left join orders o one.orderid =o.orderid
where o.orderid is null

if you want to insert all employees that have an order then your statement would be

insert sometable
select e.* from employees e join orders o one.orderid =o.orderid


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

>>>that is the problem.i can't make two procedures I need to use existing proc wich returns two sets.Then I need to insert second set into some temp table to make some calculations.It means I can't use front end apps or or create two procs.

I am not sure that you can do it straight from SQL


but you can use VB ADO code, look up multiple recordsets in BOL the code is there

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[joxa83]

No No proc I gave is just similar to my problem.As I said I have one procedure wich I can not change, then that proc returns two set, then I need second set for some calculations in my proc, than I need data from exactly that procedure wich returns two sets.