CODEWITHSUNDEEP
sqldb2
Jay Shankar Gupta
Jay Shankar Gupta

Reputation: 6088

I want to get count for rows in cursor in DB2

CREATE OR REPLACE PROCEDURE USP_TEST_ROW_COUNT(
OUT vROW_COUNT BIGINT
)
RESULT SETS 1
    MODIFIES SQL DATA 
    LANGUAGE SQL
P1: BEGIN ATOMIC
BEGIN
    DECLARE C1 CURSOR WITH RETURN FOR
    SELECT * FROM TABLE_NAME;
    OPEN C1;
    SET vROW_COUNT = CURSOR_ROWCOUNT(C1);
END;
END P1

Above is my code but it is showing Below Error

DB2 SQL error: SQLCODE: -206, SQLSTATE: 42703, SQLERRMC: C1
 Message: "C1" is not valid in the context where it is used.
 Line: 12

Please Help.

Upvotes: 1

Views: 2857

Answers (2)

Mark Barinstein
Mark Barinstein

Reputation: 12299

You may insert the results into some DGTT if you want to return all rows in the result set and return the number of output rows simultaneously:

CREATE OR REPLACE PROCEDURE USP_TEST_ROW_COUNT(OUT vROW_COUNT BIGINT)
RESULT SETS 1
MODIFIES SQL DATA 
BEGIN ATOMIC
  DECLARE v_stab VARCHAR(100) DEFAULT 'SESSION.USP_TEST_ROW_COUNT';
  DECLARE v_stmt VARCHAR(100) DEFAULT 'SELECT * FROM TABLE_NAME';
  DECLARE C1 CURSOR WITH RETURN FOR S1;

  EXECUTE IMMEDIATE 
    'DECLARE GLOBAL TEMPORARY TABLE '||v_stab||' AS ('
  ||v_stmt
  ||' ) DEFINITION ONLY WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED';
  EXECUTE IMMEDIATE 'INSERT INTO '||v_stab||' '||v_stmt;
  GET DIAGNOSTICS vROW_COUNT=ROW_COUNT;

  PREPARE S1 FROM 'SELECT * FROM '||v_stab;
  OPEN C1;
END@

Upvotes: 1

mao
mao

Reputation: 12267

CURSOR_ROWCOUNT can only return the number of rows fetched (by the caller of the stored procedure). This is different from the number of rows in the result set. So if your syntax was accepted the value would be zero initially as nothing as yet been fetched.

You can see an example here, which shows the cursor variable, the cursor being opened and fetched, and the resulting value returned by CURSOR_ROWCOUNT.

To find the number of rows in the result-set either consume the cursor (fetch until no more rows), or do a second query that counts the rows in the same unit of work, or append the count to each row and fetch only 1 row.

Upvotes: 0

Related Questions