Cursors as output parameters??

• Rehan Ahmad Rattu

  SSC Eights!

  Points: 825

  More actions

  December 27, 2003 at 3:24 am

  #85202

  Hi,

  I am looking for a solution in which I can return a RecordSet in a OUTPUT Cursor using the parameters of a Stored Procedure. This is the exact way I am looking for record sets to be available to me in the Application Layer, i.e. Cursor in the output parameters.

  help will be highly praised.

  Regards

  Rehan Rattu

  - Rattu

• Gabor Nyul

  SSCrazy Eights

  Points: 9459

  More actions

  December 29, 2003 at 3:30 am

  #487907

  See BOL:

  quote:

  ---

  E. Use an OUTPUT cursor parameter

  OUTPUT cursor parameters are used to pass a cursor that is local to a stored procedure back to the calling batch, stored procedure, or trigger.

  First, create the procedure that declares and then opens a cursor on the titles table:

  USE pubs

  IF EXISTS (SELECT name FROM sysobjects

  WHERE name = 'titles_cursor' and type = 'P')

  DROP PROCEDURE titles_cursor

  GO

  CREATE PROCEDURE titles_cursor @titles_cursor CURSOR VARYING OUTPUT

  AS

  SET @titles_cursor = CURSOR

  FORWARD_ONLY STATIC FOR

  SELECT *

  FROM titles

  OPEN @titles_cursor

  GO

  Next, execute a batch that declares a local cursor variable, executes the procedure to assign the cursor to the local variable, and then fetches the rows from the cursor.

  USE pubs

  GO

  DECLARE @MyCursor CURSOR

  EXEC titles_cursor @titles_cursor = @MyCursor OUTPUT

  WHILE (@@FETCH_STATUS = 0)

  BEGIN

  FETCH NEXT FROM @MyCursor

  END

  CLOSE @MyCursor

  DEALLOCATE @MyCursor

  GO

  ---

  Bye

  Gabor

  
  
  Bye
  Gabor

• noeld

  SSC Guru

  Points: 96590

  More actions

  December 29, 2003 at 8:02 am

  #487961

  I am not quite sure but I will try not to use this technique unless impossible to build a SET BASED solution

  
  * Noel