Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2006
    Posts
    15

    Unanswered: Dynamic SQL in Stored Procedure

    Hi, i am a new bee in Informix SQL.

    I need to construct a stored procedure which is consists of dynamic sql.
    The condition of getting the data from table is not sure until execution time.

    For example,
    The dynamic SQL can be either

    1).select pcc_name from pcc where pcc_name='ABC'
    2).select pcc_name from pcc where pcc_name='ABC' or pcc_name='DEF'
    2).select pcc_name from pcc where pcc_name='ABC' or pcc_name='GHI'
    2).select pcc_name from pcc where pcc_name='ABC' or pcc_name='GHI' or pcc_name='JKL'


    My sample stored procedure:

    create procedure BBB(Param1 char(20))

    DEFINE v_sql VARCHAR(250);

    LET v_sql = "select pcc_name from pcc where pcc_name = '" || Param1 || "'";

    EXECUTE IMMEDIATE v_sql;

    end procedure;

    but it give me syntax error at EXECUTE IMMEDIATE v_sql;
    Does anyone has any idea?
    Help Please !!!
    Last edited by johnsonlim026; 03-21-11 at 04:23.

  2. #2
    Join Date
    Sep 2010
    Location
    Germany, Brunswick
    Posts
    55
    You can only use "EXECUTE IMMEDIATE" for some kind of SQL:
    EXECUTE IMMEDIATE and Restricted Statements

    Source: EXECUTE IMMEDIATE statement

    You could make something like that:
    Code:
    create procedure get_name(customer_num int) returning char(50);
    
      DEFINE v_sql VARCHAR(250);
      DEFINE lname CHAR(50);
      
      LET v_sql = "select lname from customer where customer_num = '" || customer_num || "'";
      
      prepare xsql from v_sql;
      declare xcur cursor for xsql;
      OPEN xcur;
      FETCH xcur into lname;
      CLOSE xcur;
      FREE xcur;
      FREE xsql;
      
      RETURN lname;
    
    end procedure;
    
    select get_name(customer_num) from customer;
    WilhelmW

  3. #3
    Join Date
    Dec 2006
    Posts
    15
    Hi InformixWilli ,
    Thanks for ur help.
    i think i do not make myself clear...

    The value Param1 that is pass to the stored procedure can be 'ABC' ,
    'ABC' or pcc_name='DEF',
    'ABC' or pcc_name='GHI' or pcc_name='JKL'

    so that a complete sql statement can be formed when the sql inside stored procedure is combined with the parameter.
    for example:
    the complete sql can be formed inside stored procedure is :
    1).select pcc_name from pcc where pcc_name='ABC'
    2).select pcc_name from pcc where pcc_name='ABC' or pcc_name='DEF'

    Is it possible to do so in informix?
    The ids version of informix is 11.50..
    Help Please!!!!

  4. #4
    Join Date
    Sep 2010
    Location
    Germany, Brunswick
    Posts
    55
    No Problem:

    Code:
    create procedure get_name(v_where varchar(250)) returning char(50);
    
      DEFINE v_sql VARCHAR(250);
      DEFINE lname CHAR(50);
      
      LET v_sql = "select lname from customer where customer_num = " || v_where;
      
      prepare xsql from v_sql;
      declare xcur cursor for xsql;
      OPEN xcur;
      FETCH xcur into lname;
      CLOSE xcur;
      FREE xcur;
      FREE xsql;
      
      RETURN lname;
    
    end procedure;
    
    select get_name('110 AND fname="John"') from customer;
    WilhelmW

  5. #5
    Join Date
    Mar 2011
    Posts
    2
    so that a complete sql statement can be formed when the sql inside stored procedure is combined with the parameter.


    ___________________________________________
    Christian Louboutin On Sale
    Christian Louboutin Sale UK
    Christian Louboutin Shoes Sale

Posting Permissions

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