Hi All,

I am having an error calling the stored Procedure.This SP works fine when I execute the call from the Admin Client.It gives an error "Number of host variables ...." when it is run from the DBVisualiser or any third Pary tool or a runtime client.How can this be fixed.Please suggest.

CREATE PROCEDURE Samp.execute_immediate (IN in_stmt VARCHAR(1000) ,OUT out_sqlcode INTEGER)
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN
DECLARE sqlcode INTEGER;
DECLARE EXIT HANDLER FOR sqlexception
SET out_sqlcode = sqlcode;
EXECUTE IMMEDIATE in_stmt INTO sqlcode;
SET out_sqlcode = sqlcode;
RETURN;
END

CALL Samp.execute_immediate (' UPDATE Samp.Employee SET STATUS = ''' || 'N'' ; ',?);

Thanks,

without further information, this is more like a guess:
did you provide the value for the parameter marker? the error msg seems complain that DB2 didn't receive a value for the parameter marker(the 2nd argument of your stored proc).

You might be having a problem with the embedded terminator character, ';'. Try concatenating the hex value for the terminator character. I think it's x'59'.

I have provided the complete Information.I just created the SP as it is shown above and executed the call.I get the following error
[CALL - 0 row(s), 0.031 secs] [Error Code: -313, SQL State : 07004] The number of host variables in the EXECUTE or OPEN statement is not equal to the number of values required.

... 1 statement(s) executed, 0 row(s) affected, database exec time 0.031 sec [0 successful, 0 warnings, 1 errors]

I did not provide any value for the parameter marker as it is an output paramter for the SQLCODE which is returned .SET out_sqlcode = sqlcode.
I executed and as I said it works on the DB2 Administration Client without the error.It gives the error with runtime clietns.