Results 1 to 4 of 4
  1. #1
    Join Date
    May 2014
    Posts
    2

    Arrow Unanswered: if CONDITION in store procedure of dynamic SQL creation ,Informix

    Hi Everyone this is sneha ,


    I am new to informix .
    I have created store procedure as mentioned below . Its excuted successfully
    Now when i run the SQL i am not able to got into IF else condition

    I want cust_qry to set when if conditin passes.
    Cust_qry only having below SQL
    Code:
    select pt.paramenter1,pt.parameter2,pt.parameter3,pt.parameter4,pt.parameter5,pt.parameter6 from penltxns pt, outer X ps   where pt.set_no = ps.set_no and   pt.paramenter1 = '" || parIn ||
    I want to add and condition as per if else condition.





    Code:
    CREATE PROCEDURE SP_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 penltxns pt, outer X ps   where pt.set_no = ps.set_no and   pt.paramenter1 = '" || parIn || "' ";
      
        IF	  parIn2 != '' " THEN
    	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;
      CLOSE cust_cur;
      FREE cust_cur ;FREE stmt_id ;
     END PROCEDURE;
     execute procedure sp_gim_pending_trnx_table_info('WL61280','','','SL')
    Please help Informix DB guys

  2. #2
    Join Date
    Sep 2011
    Location
    Pont l'Abbé, Brittany, France
    Posts
    381
    Provided Answers: 1
    Hi
    What is you problem?
    Do you have a error at compile time ? I can see a reason why you could have one error
    IF parIn2 != '' " THEN (2 simple quotes ' followed by a double quote " character)

    else if this is a wrong execution problem, you can debug your stored procedure to a file
    Check the doc at this place
    and see what happens

    Please note that if you are on or above version 11.50 xC1, you can use DYNAMIC statements within stored procedures ( PREPARE.. EXECUTE )
    Check here

    And finally a stupid but necessary question: did you drop and recreate your stored procedure ?

    Have fun
    Eric

  3. #3
    Join Date
    May 2014
    Posts
    2

    Red face

    HI Eric,

    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 || "' " ;


    so that my query should be dynamix and resulted query should be as below
    as i have passes both parIn /parIn2

    select pt.paramenter1,pt.parameter2,pt.parameter3,pt.para meter4,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 || "'" ;

    But problem is its giving me query till only

    select pt.paramenter1,pt.parameter2,pt.parameter3,pt.para meter4,pt.parameter5,pt.parameter6 from X pt, outer X ps where pt.set_no = ps.set_no and pt.paramenter1 = '" || parIn || "'

    its not considering parIn2 even when its not null .. Please help

  4. #4
    Join Date
    Sep 2011
    Location
    Pont l'Abbé, Brittany, France
    Posts
    381
    Provided Answers: 1
    Use the debugger, it will give you the detail of the instructions executed and the values set and used.

    You also can use the stored procedure debugger featured in Server Studio from AGS

    Check here

    There might might some confusion with NULL values and ''

Posting Permissions

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