Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to Access Cursor Columns Without FETCH .. INTO

I'm using SQL Server to build stored procedures, and I'm using cursors to loop through a select statement

I'm defining the cursor as follow:

DECLARE @c_col1 varchar(max);
DECLARE @c_col2 varchar(max);

DECLARE c as CURSOR FOR 
SELECT col1, col2 
FROM table;

OPEN c;
FETCH NEXT FROM c INTO
@c_col1, @c_col2;

SELECT @c_col1, @c_col2;

Is there a way to access the columns of the cursor without a need to declare variables for each column and to use INTO in FETCH clause? In other words, is it possible to use:

DECLARE c as CURSOR FOR 
SELECT col1, col2 
FROM table;

OPEN c;
FETCH NEXT FROM c; 

SELECT c.col1, c.col2;
like image 787
ala Avatar asked Aug 19 '09 08:08

ala


People also ask

How to fetch columns in cursor using FETCH?

- Stack Overflow How to fetch columns in cursor using FETCH? create or replace PROCEDURE newprocedur (outname OUT VARCHAR2,outroll OUT NUMBER) AS CURSOR c1 IS select Name,Rollno,Section from emp; BEGIN Open c1; fetch c1 into outname,outroll; Here out of 3 columns in cursor is it possible to fetch only two columns using FETCH like i did above??

What is the use of fetchall in cursor?

cursor.fetchall() fetches all the rows of a query result. It returns all the rows as a list of tuples. It returns all the rows as a list of tuples. An empty list is returned if there is no record to fetch.

How to fetch rows from a PL/SQL cursor?

The FETCH statement that is required to fetch rows from a PL/SQL cursor is supported by the data server in PL/SQL contexts. Name of a static cursor or cursor variable. Identifier for a previously-defined record. This can be a user-defined record or a record definition that is derived from a table using the %ROWTYPE attribute.

How do I retrieve the next record from the cursor?

The FETCH statement retrieves the next record from the result set of the SELECT statement associated with the DECLARE statement of the cursor. You need to provide the variables list into which the values of the retrieved record are intended to be stored.


1 Answers

No, you have to do it that way if you want to store the values from the cursor in local variables instead of returning them back to the client.

like image 167
Eric Z Beard Avatar answered Oct 04 '22 00:10

Eric Z Beard