Dynamic SQL in Stored Procedure

johnsonlim026 · #1 (**permalink**) 03-21-11, 03:04

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 !!!

InformixWilli · #2 (**permalink**) 03-21-11, 04:39

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;

johnsonlim026 · #3 (**permalink**) 03-21-11, 05:12

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!!!!

InformixWilli · #4 (**permalink**) 03-21-11, 06:46

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;

zhucelun789 · #5 (**permalink**) 03-31-11, 01:15

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

Internet Services

Web Development

Digital Marketing

Consumer Tech