Results 1 to 2 of 2

Thread: drop procedure

  1. #1
    Join Date
    Jun 2003
    Posts
    7

    Unanswered: drop procedure

    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

  2. #2
    Join Date
    Dec 2002
    Posts
    134

    Re: drop procedure

    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

Posting Permissions

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