Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2007
    Location
    Nantes, France
    Posts
    2

    Unanswered: dynamic sql in stored procedure

    Hello,

    I seek a method to fetch a cursor whose request is contained in a variable. The following code is ok in a 4GL but not in a stored procedure. I use version 9.40 of informix.

    Thank you for your help


    Code:
    CREATE PROCEDURE sp_get_nom_calc_sect(pi_instance VARCHAR(100))
    RETURNING       CHAR(2);
     
    DEFINE  ls_sql                  char(100);
    DEFINE  po_cd_act_sect          LIKE nom_calc_sect.cd_sect_act;
     
     
    let ls_sql = "SELECT cd_sect_act from " || pi_instance;
     
    prepare statement2 FROM ls_sql
    declare cur_calc_sect cursor FOR statement2
    FOREACH cur_util INTO po_cd_act_sect
     
            RETURN  po_cd_act_sect WITH RESUME;
     
    END FOREACH
     
    END PROCEDURE;

  2. #2
    Join Date
    Aug 2003
    Location
    India
    Posts
    262
    You can try the below code. This was posted by one of our boarders.


    create table testtab (f1 smallint,f2 char(3));

    and here goes a demo stored procedure:

    create procedure testtab_proc( mf1 char(8), mf2 char(7))
    define cnt int;
    set lock mode to wait;
    while 1=1
    insert into testtab values (mf1, mf2);
    select dbinfo('sqlca.sqlerrd2') into cnt from
    systables where tabid=1;
    if cnt is null then
    let cnt=0;
    end if
    if cnt = 1 then
    exit while;
    end if
    end while
    end procedure;

    and finally this is how you may invoke the SPL using SQL:

    execute procedure testtab_proc(1,'AAA');

Posting Permissions

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