Post reply

Cursors as output parameters??

  • 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

  • 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

  • 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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply