Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2009
    Posts
    5

    Unanswered: Setting out parameters in SP with if & elseif

    Hi guys, I was try execute this code
    Code:
    CREATE PROCEDURE test (IN CODE varchar(10), OUT MSG varchar(20))
      LANGUAGE SQL
      DYNAMIC RESULT SETS 1 
    BEGIN
    	
    	IF CODE = 'A123' THEN
    		SET MSG = 'HELLO IM NEWBIE';
    	ELSEIF CODE= 'A124' THEN
                   SET MSG = 'BYE IM NEWBIE';
    	ELSE THEN
    		SET MSG = 'CODE NOT EXIST';
    		
    	END IF;
    
    END;
    When I try execute this procedure console gave me : SQL0206N (not valid context). What thing i'm doing wrong?????
    Last edited by xerg; 08-14-09 at 11:52.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I am confused. Do you get the error when you try to create the Stored Procedure or when you execute it? If it is when you execute it, please provide the exact call statement and the full error. Also include DB2 version and OS.

    Andy

  3. #3
    Join Date
    Aug 2009
    Posts
    5
    Quote Originally Posted by ARWinner
    I am confused. Do you get the error when you try to create the Stored Procedure or when you execute it? If it is when you execute it, please provide the exact call statement and the full error. Also include DB2 version and OS.

    Andy
    You're right, I got that error when I was try CREATE PROCEDURE, sorry for that

    Info about db2.
    Product identifier SQL09050
    Level identifier 03010107
    Level DB2 v9.5.0.808
    Build level s071001
    PTF NT3295

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What is the full error message you are getting?

    Andy

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Code:
    	ELSE THEN
    		SET MSG = 'CODE NOT EXIST';
    Your code has syntax error.
    "THEN" is not neccessary.

  6. #6
    Join Date
    Aug 2009
    Posts
    5
    Thanks you!

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    And you also shouldn't use ';' as statement terminator for the whole CREATE PROCEDURE statement - only for the statements inside the procedure body.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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