Search code examples
stored-proceduresdb2db2-luw

DB2 Stored Procedure Not able to assign data to a variable

I have a simple stored procedure to calculate the sum of salaries of employees, sum of their squares and number of rows.

This is the stored procedure I have written:

I get an error in fetching the number of rows from the database and assigning it to a variable. What do I do? Using DB2 11.5

Solution

  • It helps to specify the exact error code when asking questions (don't write get an error, do write instead 'get error SQL0104N ...`.

    Your mistake is that you have not followed the documented order for SQL statements in compound SQL blocks.

    The SELECT statement can only appear after any cursor definitions, local procedures , and handlers if you have any.

    So move the statement SELECT COUNT(*) INTO TOTAL_ROWS FROM EMPLOYEE; so that it appears after the DECLARE CURSOR1 ... line, the try to recompile.