Icon SET PROGRESS

Creates a new progress update.

Syntax
SET PROGRESS TO <CompletionPercent>

Usage
Use this statement to create a progress update that can be displayed by the application that is executing the current routine. The completion percentage should be be any valid percentage between 1 and 100.

You can use the ABORTED SQL/PSM function to determine if the application indicated that it wished to abort the current execution in response to the progress update.

Examples
-- This procedure uses a SET PROGRESS
-- statement to display progress during its
-- execution and uses the ABORTED function
-- to abort the execution if the application
-- requests it

CREATE PROCEDURE UpdateState()
BEGIN
   DECLARE CustCursor CURSOR WITH RETURN FOR Stmt;
   DECLARE State CHAR(2) DEFAULT '';
   DECLARE TotalRows INTEGER DEFAULT 0;
   DECLARE NumRows INTEGER DEFAULT 0;

   PREPARE Stmt FROM 'SELECT * FROM Customer';

   OPEN CustCursor;
   SET TotalRows=ROWCOUNT(CustCursor);

   START TRANSACTION ON TABLES 'Customer';
   BEGIN

      FETCH FIRST FROM CustCursor ('State') INTO State;

      WHILE (NOT (EOF(CustCursor) OR ABORTED)) DO
         IF (State='FL') THEN
            UPDATE CustCursor SET 'State'='NY';
         END IF;
         FETCH NEXT FROM CustCursor ('State') INTO State;
         SET NumRows=NumRows+1;
         SET PROGRESS TO TRUNC(((NumRows/TotalRows)*100));
      END WHILE;

      IF (NOT ABORTED) THEN
         COMMIT;
      ELSE
         ROLLBACK;
      END IF;

   EXCEPTION
      ROLLBACK;
   END;
END

SQL 2003 Standard Deviations
This statement deviates from the SQL 2003 standard in the following ways:

DeviationDetails
ExtensionThis SQL statement is an ElevateDB extension.
Image