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!)
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