Progress
SQL-89
Guide and Reference


Retrieving Values from the Retrieval Set

The FETCH statement has the following general syntax.

SYNTAX
FETCH cursor-name INTO variable-list 

The variable-list is a list of procedure variables separated by commas. There must be one variable for each column listed with SELECT in the DECLARE CURSOR statement. The data types of the variables and columns must be assignment compatible. Do not use the same name to specify procedure variables and SQL columns.

When referring to the values fetched into the list of variables, use the procedure variable name into which the list of variables was fetched. Do not use the column name from the DECLARE CURSOR statement.

NOTE

The FETCH statement retrieves the next row from the retrieval set and moves each column value to the corresponding variable. There can be more than one FETCH statement for an open cursor, each with its own list of variables, as long as the number and data types of the variables correspond to the cursor definition.

In the following example, the customer name and number are fetched into the procedure variables, var1 and var2.

FETCH c1 INTO var1, var2. 

If the DECLARE CURSOR statement specifies SELECT *, the number of variables in the list of variables must match the column count that the asterisk represents.

Each time you execute the FETCH statement, it retrieves a single row in the retrieval set. To fetch all the rows in a retrieval set, you must include the FETCH statement in a Progress looping block. For example, you could place the FETCH statement within a REPEAT block to fetch multiple rows, one row at a time.

When including FETCH in a repeat loop, the DISPLAY variables statement must appear inside of the loop to see the data for each row.

DEF VAR var1 LIKE Ncust.Cust-Num.
DEF VAR var2 LIKE Ncust.Name.

OUTPUT TO out.file PAGED PAGE-SIZE 60.
DECLARE c1 CURSOR FOR SELECT * FROM Ncust.
OPEN c1.
  REPEAT:
    FETCH c1 INTO var1, var2.
    DISPLAY var1 var2.
  END.

END. 


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095