CODEWITHSUNDEEP
loopsfor-loopdynamiccursordb2
user2632788
user2632788

Reputation: 3

Using a Dynamic SQL in the CURSOR's FOR LOOP

After hours of searching, I can't seem to find the solution to this simple thing!

  CREATE PROCEDURE P()
  LANGUAGE SQL
  BEGIN ATOMIC
    DECLARE fullname CHAR(40);

    FOR v AS cur1 CURSOR FOR 
                SELECT firstnme, midinit, lastname FROM employee
    DO 
      SET fullname = v.lastname || ',' || v.firstnme 
                     ||' ' || v.midinit;
      INSERT INTO tnames VALUES (fullname);
    END FOR;
  END

The above block of DB2 stored procedure uses a FOR loop for a STATIC SQL, I have tried replacing the SQL with a Dynamic SQL variable but it does not work. Any help would be great.

I know we can use OPEN..FETCH..LOOP block to do the same, but my requirement is to use a FOR Loop for this.

Many thanks :) Faisal.

Upvotes: 0

Views: 886

Answers (2)

WarrenT
WarrenT

Reputation: 4532

FOR is a static, compiled statement that does not allow a prepared statement name to be used in place of the SELECT statement.

You may want to review your interpretation of the requirements, or think outside the box to approach this from an entirely new angle.

Upvotes: 0

Barmar
Barmar

Reputation: 780871

You can do this with a single INSERT ... SELECT statement:

INSERT INTO tnames
SELECT lastname || ',' || firstnme ||' ' || midinit
FROM employee;

Upvotes: 1

Related Questions