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)

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



Show quoteHide quote

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.

Show quoteHide quote

Return only the Third Row
Choosing one record over another
can't return zero when no rows exist using aggregate function sum
Count CHAR(11) in a string
Table name as a variable