title: how to Set variable in INFORMIX DB according to IF else condtion for a dynamic SQL
body: <pre><code>    CREATE PROCEDURE Table_info
  (parIn CHAR(12), parIn2 CHAR(12), parIn3 CHAR(20),parIn4 CHAR(2))
  RETURNING CHAR(12) AS paramenter1,CHAR(23) AS parameter2,CHAR(2) AS parameter3,CHAR(12) AS parameter4,CHAR(1) AS parameter5,INTEGER AS parameter6 ;
  DEFINE paramenter1 CHAR(12);
  DEFINE parameter2 CHAR(22);
  DEFINE parameter3 CHAR(2);
  DEFINE parameter4 CHAR(12);
  DEFINE parameter5 CHAR(1);
  DEFINE parameter6 INTEGER;
  DEFINE cust_qry   CHAR(1024);
  LET cust_qry = "select pt.paramenter1,pt.parameter2,pt.parameter3,pt.parameter4,pt.parameter5,pt.parameter6              from X pt, outer X ps   where pt.set_no = ps.set_no and   pt.paramenter1 = '" || parIn || "' ";
  
  IF    parIn2 = '' THEN
  LET cust_qry = cust_qry ;
  ELSE
  LET cust_qry = cust_qry || "and pt.parameter4 = '"|| parIn2 || "' " ;
  END IF;
  if parIn3 != '' then 
  LET cust_qry = cust_qry ||"and pt.trd_blk_ref = '"|| parIn3 ||"' " ;    
  END IF;
  if ( parIn4 != '0') then 
  LET cust_qry = cust_qry ||"and pt.parameter3 = '"|| parIn4 ||  "' " ;
  END IF;
  
         PREPARE stmt_id FROM cust_qry;
     DECLARE cust_cur cursor FOR stmt_id;
     OPEN cust_cur; 
       WHILE (1 = 1)
       FETCH cust_cur INTO paramenter1, parameter2,parameter3,parameter4,parameter5,parameter6;
       if (sqlcode = 100 ) then exit; end if;
        RETURN paramenter1,parameter2,parameter3,parameter4,parameter5,parameter6 WITH RESUME ;
          end while;
  </code></pre>
  
  <p>I have cerated a store procedur in Informix DB .It has created successfully without any error .
  and excutes without anyerror .
  Only problem is it is not recongnizing IF else condition where i have added 
      LET cust_qry = cust_qry || "and pt.parameter4 = '"|| parIn2 || "' " ;</p>
  
  <p>Please help me :(</p>
  
  <p>I have no problem in compiling and excuting Store Procedure its just that even if i pass parameter 1 and parameter 2 to store procedure its not going into if condition where i have mentioned 
  IF    parIn2 != '' THEN
  LET cust_qry = cust_qry || "and pt.parameter4 = '"|| parIn2 || "' " ;</p>
  
  <p>so that my query should be dynamix and resulted query should be as below 
  as i have passes both parIn /parIn2</p>
  
  <p>select pt.paramenter1,pt.parameter2,pt.parameter3,pt.parameter4,pt.parameter5,pt.parameter6  from X pt, outer X ps where pt.set_no = ps.set_no and   pt.paramenter1 = '" || parIn || "' and pt.parameter4 = '"|| parIn2 || "'" ;</p>
  
  <p>But problem is its giving me query till only </p>
  
  <p>select pt.paramenter1,pt.parameter2,pt.parameter3,pt.parameter4,pt.parameter5,pt.parameter6  from X pt, outer X ps where pt.set_no = ps.set_no and   pt.paramenter1 = '" || parIn || "' </p>
  
  <p>its not considering parIn2 even when its not null .. Please help</p>