Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Location
    Bangalore
    Posts
    36

    Unanswered: Dynamic sql in DB2 Cursor select statement

    I want to pass a dynamic select clause in the Cursor FOR SELECT statement.

    e.g,
    DECLARE carrier_cur CURSOR WITH RETURN FOR
    SELECT carriername, effectivedate
    FROM Carrier;

    Instead of specifying the two fields at design time, I want it to be framed at runtime where I have the options to select the fileds based on requirement.

    Is it possible to do using DB2 SQL statements?

  2. #2
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    It certainly is...

    CREATE PROCEDURE SP_TEST
    (
    IN P_SELECT_FIELDS VARCHAR(50)
    )
    LANGUAGE SQL

    P1: BEGIN
    DECLARE V_DYNAMIC VARCHAR(100);
    DECLARE V_SQL VARCHAR(100);

    DECLARE CARRIER_CUR CURSOR WITH RETURN FOR V_DYNAMIC;

    SET V_SQL = 'SELECT ' || P_SELECT_FIELDS || ' FROM CARRIER;'

    PREPARE V_DYNAMIC FROM V_SQL;

    OPEN CARRIER_CUR;
    END P1
    @

    CALL SP_TEST ('FIELD1, FIELD2')
    @
    Last edited by Damian Ibbotson; 01-15-03 at 06:07.

  3. #3
    Join Date
    Jan 2003
    Location
    Bangalore
    Posts
    36
    Thanks a lot,Damian.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •