Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2003
    Posts
    30

    Unanswered: Running a procedure from a procedure

    Hi,
    Lets say I have procedures called AAA_INS_UPS_DEL, BBB_INS_DEL_UPD...etc
    AAA, BBB etc are the table names.
    How can I run these procedures dynamically from the procedure A123.
    The procedure listed below will help you understand what I am trying to do.

    Thanks for the help.
    -Soumil

    ****************************
    Create procedure a123
    declare v_procname varchar(100);
    Declare v_tablename varchar(100);
    Declare SQLSTATE VARChar(5);
    Declare v_param1 varchar(15);
    Declare v_param2 varchar(15);
    Declare cur_tblname cursor for
    select name from ListOfTables;

    open cur_tblname;

    Fetch cur_tblname into v_tablename;
    While (SQLSTATE = '00000') do

    SEt v_param1 = 'InputParameter1';
    set v_param2 = 'InputParameter2';
    set v_procname = v_table_name || '_ins_upd_del ' || v_param1 || ' , ' || v_param2 ;

    -- How do I run the procedures named after the TABLENAMES
    -- For Eg Lets say we have three tables AAA, BBB CCC
    -- The Cursor would pick up one table at a time and
    -- the string v_procname will be 'AAA_ins_upd_del'

    call v_procname ;


    End While;
    Close cur_tblname;

    end

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Running a procedure from a procedure

    See if this thread helps

    http://dbforums.com/showthread.php?threadid=873480

    Sathyaram



    Originally posted by soumil
    Hi,
    Lets say I have procedures called AAA_INS_UPS_DEL, BBB_INS_DEL_UPD...etc
    AAA, BBB etc are the table names.
    How can I run these procedures dynamically from the procedure A123.
    The procedure listed below will help you understand what I am trying to do.

    Thanks for the help.
    -Soumil

    ****************************
    Create procedure a123
    declare v_procname varchar(100);
    Declare v_tablename varchar(100);
    Declare SQLSTATE VARChar(5);
    Declare v_param1 varchar(15);
    Declare v_param2 varchar(15);
    Declare cur_tblname cursor for
    select name from ListOfTables;

    open cur_tblname;

    Fetch cur_tblname into v_tablename;
    While (SQLSTATE = '00000') do

    SEt v_param1 = 'InputParameter1';
    set v_param2 = 'InputParameter2';
    set v_procname = v_table_name || '_ins_upd_del ' || v_param1 || ' , ' || v_param2 ;

    -- How do I run the procedures named after the TABLENAMES
    -- For Eg Lets say we have three tables AAA, BBB CCC
    -- The Cursor would pick up one table at a time and
    -- the string v_procname will be 'AAA_ins_upd_del'

    call v_procname ;


    End While;
    Close cur_tblname;

    end
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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