Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2011
    Posts
    32

    Question Unanswered: varchar and null

    My question is simple. Why the first example works and the second one doesnt? The second one is a big big sp, but i wanted to debug it as block by block. but I am unable to find the error. Pl advise.

    first sp.(which is working fine)
    Code:
    CREATE PROCEDURE test_sp
    (strSource varchar(5)) returning char(255);
    BEGIN
    
    define retVal char(255);
           
     if (strSource != 'null')
            then let strSource = 'null';
            let retVal = "hi";
     END if;
     
    return retVal;
    END;
    END PROCEDURE;
    second sp (throwing Syntax error... which line number, cant understand as iam using db visualizer)
    Code:
    create procedure sp_getTimeOffSet(pass_zipCode  VARCHAR(5)) returning INTEGER;
    
    BEGIN
    
    DEFINE t_timeZone INTEGER;
    
    LET  t_timeZone = 0;
    
    IF (pass_zipCode != 'null')
    
    	
    		THEN let pass_zipCode = 'null';
    
    end if;
    
    return t_timeZone;
    
    end;
    end procedure;
    pl let me know if there's anything wrong Thanks.

  2. #2
    Join Date
    Sep 2011
    Location
    Pont l'Abbé, Brittany, France
    Posts
    381
    Provided Answers: 1
    Please follow the upcoming example (in next post ) which will show you how to correctly handle NULL values in Informix stored procedures

  3. #3
    Join Date
    Sep 2011
    Location
    Pont l'Abbé, Brittany, France
    Posts
    381
    Provided Answers: 1
    here is the example. Look carefully. You must not use the NULL word with quotes, else it is considered as a regular string, not the NULL value.
    Code:
    --drop procedure myproc ;
    create procedure myproc (pass CHAR(10)) returning CHAR(10), CHAR(10)
    DEFINE mystatus char (10) ;
    LET mystatus = "InitValue" ;
    if pass IS NULL THEN
       LET pass="NullValue" ;
       LET mystatus = "notexpected" ;
    ELSE
       LET mystatus = NULL ;
    END IF;
    return pass,mystatus;
    END PROCEDURE;
    
    execute procedure myproc ("mypass");
    execute procedure myproc (NULL);

  4. #4
    Join Date
    Aug 2011
    Posts
    32
    Hi,

    I had tried even without quotes, but it wasnt stopping from throwing error. Then I found out that a case block following this if had some issues. Fixed it and now it is working fine. Thank you.

Tags for this Thread

Posting Permissions

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