feeding results from one SP to anohter

Hi,
I need an SP to get the resultset output of another SP.
ie
create Procedure A
as
begin
select * from Area
end
Now in procedure B, how can I get these results into a cursor form processin
g?
Also, now to through another slant on it, what if Procedure A is in a remote
database, does the same logic apply?
Thanks,
Steve
(similar to a previous post today - sorry)One way to do would be to use global temporary tables (## prefix).
But this is more like a procedural programming approach which is more
suitable for client side as opposed to database side.
Could you please elaborate more on what you are trying to do.
Here is a sample code.
HTH...
use pubs
go
-- ========================================
=====
-- Create procedure basic template
-- ========================================
=====
-- creating the store procedure
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'mytestprocA'
AND type = 'P')
DROP PROCEDURE dbo.mytestprocA
GO
CREATE PROCEDURE dbo.mytestprocA
AS
drop table ##global_temp
select top 5 * into ##global_temp from authors
GO
-- ========================================
=====
-- example to execute the store procedure
-- ========================================
=====
EXECUTE dbo.mytestprocA
GO
use pubs
go
-- ========================================
=====
-- Create procedure basic template
-- ========================================
=====
-- creating the store procedure
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'mytestprocB'
AND type = 'P')
DROP PROCEDURE dbo.mytestprocB
GO
CREATE PROCEDURE dbo.mytestprocB
AS
DECLARE temp_Cursor CURSOR FOR
select * from ##global_temp
OPEN temp_Cursor
FETCH NEXT FROM temp_Cursor
WHILE @.@.FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM temp_Cursor
END
CLOSE temp_Cursor
DEALLOCATE temp_Cursor
GO
-- ========================================
=====
-- example to execute the store procedure
-- ========================================
=====
EXECUTE dbo.mytestprocB
GO
http://zulfiqar.typepad.com
BSEE, MCP
"Steve" wrote:

> Hi,
> I need an SP to get the resultset output of another SP.
> ie
> create Procedure A
> as
> begin
> select * from Area
> end
> Now in procedure B, how can I get these results into a cursor form process
ing?
> Also, now to through another slant on it, what if Procedure A is in a remo
te
> database, does the same logic apply?
> Thanks,
> Steve
> (similar to a previous post today - sorry)
>|||Best way to do this is change proc A to a function.
2nd best:
insert #table
Exec A
I would also ask what you are using the cursor for, but that is only because
cursors are evil :) Seriously most every use for a cursor has a far easier
to manage set based solution, and that is what we are here to help you with.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:8BA898FD-43B9-401C-83DB-BD314A2EAA19@.microsoft.com...
> Hi,
> I need an SP to get the resultset output of another SP.
> ie
> create Procedure A
> as
> begin
> select * from Area
> end
> Now in procedure B, how can I get these results into a cursor form
> processing?
> Also, now to through another slant on it, what if Procedure A is in a
> remote
> database, does the same logic apply?
> Thanks,
> Steve
> (similar to a previous post today - sorry)
>