Setting out parameters in SP with if & elseif

xerg · #1 (**permalink**) 08-14-09, 10:48

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

ARWinner · #2 (**permalink**) 08-14-09, 11:39

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

xerg · #3 (**permalink**) 08-14-09, 11:54

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

ARWinner · #4 (**permalink**) 08-14-09, 12:23

What is the full error message you are getting?

Andy

tonkuma · #5 (**permalink**) 08-14-09, 13:21

Code:

	ELSE THEN
		SET MSG = 'CODE NOT EXIST';

Your code has syntax error.
"THEN" is not neccessary.

xerg · #6 (**permalink**) 08-14-09, 17:04

Thanks you!

stolze · #7 (**permalink**) 08-17-09, 05:11

And you also shouldn't use ';' as statement terminator for the whole CREATE PROCEDURE statement - only for the statements inside the procedure body.

Internet Services

Web Development

Digital Marketing

Consumer Tech