Can I pass a cursor in a procedure? Can I pass a cursor in a procedure? sql sql

Can I pass a cursor in a procedure?

I am assuming you are using Oracle (it would appear so).

You can do this:

PROCEDURE abc( p_cursor IN SYS_REFCURSOR) IS   v_sol_id blt.sol_id%TYPE;   v_bill_id blt.bill_id%TYPE;   v_bank_id blt.bank_id%TYPE;BEGIN   LOOP      FETCH p_cursor INTO v_sol_id, v_bill_id, v_bank_id;      EXIT WHEN p_cursor%NOTFOUND;      ...   END LOOP;END;

Then use it:

DECLARE   v_cursor SYS_REFCURSOR;BEGIN   OPEN v_cursor FOR      SELECT  BLT.sol_id,              BLT.bill_id,              BLT.bank_id      FROM BLT;   abc (v_cursor);   CLOSE v_cursor;END;

However, note that the procedure abc needs to know the structure of the cursor, i.e. that it returns 3 columns of particular types. If you wanted to be able to pass any cursor to the procedure then you'd need to look at using the DBMS_SQL package (and that's not trivial!)

From MSDN (SQL Server 2008): The cursor data type can be used only on OUTPUT parameters. When you specify a cursor data type, the VARYING and OUTPUT keywords must also be specified. You can have multiple output parameters specified with the cursor data type.

Regarding MS-SQL you cannot pass the cursor as the input parametre. You can pass the cursor as the output parametre only.
What you can do is to pass the cursor select clause text as an input parametre. You might find the working piece of code below useful.

--Create the table and fill it with dataDROP   TABLE    dbo.StackOverflow_MyTableGOCREATE TABLE    dbo.StackOverflow_MyTable (                MyChar      varchar(10),                MyDate      datetime,                MyNum       numeric(10,2)                PRIMARY KEY (MyChar))GOINSERT INTO dbo.StackOverflow_MyTable SELECT 'A1', '2018-01-13', 123.45INSERT INTO dbo.StackOverflow_MyTable SELECT 'B2', '2018-01-14', 123.46INSERT INTO dbo.StackOverflow_MyTable SELECT 'C3', '2018-01-15', 123.47GO/* Create the procedure which returns the cursor variable based on select statement   The cursor must be opened here. Otherwise it throws an Error:   The variable '@MyCursorVar' does not currently have a cursor allocated to it*/DROP   PROCEDURE dbo.StackOverflow_OpenCursorGOCREATE PROCEDURE dbo.StackOverflow_OpenCursor @SelectSQL nvarchar(128), @MyCursorVar CURSOR VARYING OUTPUT    AS    DECLARE @SQL nvarchar(256)    SET @SQL='  SET     @MyCursorVar = CURSOR FOR '+@SelectSQL+'                OPEN    @MyCursorVar'    EXEC sp_executesql @SQL, N'@MyCursorVar CURSOR OUTPUT', @MyCursorVar OUTPUTGO--Create the procedure which browses the table using the cursor variableDROP   PROCEDURE dbo.StackOverflow_BrowseCursorGOCREATE PROCEDURE dbo.StackOverflow_BrowseCursor @SelectSQL nvarchar(128)    AS    --Create the cursor variable based on select statement and OPEN the cursor    DECLARE @MyCursorVar CURSOR    EXEC dbo.StackOverflow_OpenCursor @SelectSQL, @MyCursorVar OUTPUT    --Declare the variables corresponding to table column    DECLARE @MyChar varchar(10), @MyDate datetime, @MyNum numeric(10,2)    --Browse record by record    WHILE 1=1    BEGIN        FETCH NEXT FROM @MyCursorVar INTO @MyChar, @MyDate, @MyNum        IF @@FETCH_STATUS <> 0 BREAK        PRINT @MyChar   --Here you might call any other procedure or dataset update        PRINT @MyDate        PRINT @MyNum    END    --release the cursor resources    CLOSE       @MyCursorVar    DEALLOCATE  @MyCursorVarGO--How to call the cursor browsing DECLARE @SelectSQL nvarchar(128)SET     @SelectSQL = 'SELECT MyChar, MyDate, MyNum FROM dbo.StackOverflow_MyTable ORDER BY MyChar'EXEC dbo.StackOverflow_BrowseCursor @SelectSQL