I am using db2/as400 v5r1. I am not sure why this stored procedure is not working. I would appreciate if someone can help me.

Thanks

create procedure dropproc
(in i_proc_name varchar(100)
,in i_lib_name varchar(50)
)
language sql
begin
declare v_stmt varchar(200);

if (1 = (select count(procname)
from qsys2.procedures
where definer = user
and procschema = i_lib_name
and procname = i_proc_name)) then
set v_stmt = concat('DROP PROCEDURE ',i_proc_name);

--set v_stmt = concat(v_stmt,';');



prepare s1 from v_stmt;

execute s1;
end if;

end

I do not know about AS/400 and v5, but I do see couple of the mistakes:

1. It's possible to have multiple stored procedures with the same name and schema
2. Drop procedure should include schema name, and idealy in case of multiple sp with the same, it shoud be "drop scpecific procedure schema.specificname" as specific is unque

regards,
dmitri