Results 1 to 2 of 2
  1. #1
    Join Date
    May 2004
    Posts
    6

    Unanswered: stored procedure call problem

    Hi I am calling one procedure from another procedure
    now the called procedure throws some exception i want to catch
    that exception and continue with some actions .Could you please help me out in this .My code is :
    CREATE PROCEDURE PROC_DELETE_PROJECTS (IN P_PROJECT_NAME VARCHAR(35),OUT counter INT)
    DYNAMIC RESULT SETS 1
    LANGUAGE SQL

    p1: BEGIN

    DECLARE v_ProjectId INT DEFAULT 0;
    DECLARE v_VariableId INT DEFAULT 0;
    DECLARE v_DataConnectionId INT DEFAULT 0;
    DECLARE v_Count INT DEFAULT 0;
    DECLARE v_counter INT DEFAULT 0;
    DECLARE var_count INT DEFAULT 0;
    DECLARE del_count INT DEFAULT 0;
    DECLARE SQLSTATE CHAR(5);

    DECLARE not_found
    CONDITION for SQLSTATE '02000';

    DECLARE ERROR_DELETE_VARIABLE
    CONDITION FOR SQLSTATE '42501';


    DECLARE at_end SMALLINT DEFAULT 0;



    DECLARE c_ProjectVariables CURSOR FOR
    select projvar_xref.variable_id
    from projvar_xref
    where projvar_xref.project_id= v_ProjectId
    ORDER BY variable_id DESC;

    DECLARE CONTINUE HANDLER for not_found
    SET at_end = 1;

    DECLARE CONTINUE HANDLER for ERROR_DELETE_VARIABLE
    SET at_end = 2;

    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    SET at_end = 3;


    SET counter=0;

    SELECT PROJECT_MDI.ID
    INTO v_ProjectId
    FROM PROJECT_MDI
    WHERE PROJECT_MDI.NAME = P_PROJECT_NAME ;

    SELECT DATACONNECTION_ID
    INTO v_DataConnectionId
    FROM DATAPROJ_XREF
    WHERE PROJECT_ID=v_ProjectId;

    select projvar_xref.variable_id
    INTO var_count
    from projvar_xref
    where projvar_xref.project_id= v_ProjectId
    ORDER BY variable_id DESC;


    DELETE FROM DATAPROJ_XREF WHERE DATAPROJ_XREF.PROJECT_ID = v_ProjectId;
    DELETE FROM DATACONNECTION_MDI WHERE ID=v_DataConnectionId;
    DELETE FROM CUPROJ_XREF WHERE CUPROJ_XREF.PROJECT_ID = v_ProjectId;
    WHILE (del_count <> var_count) DO
    SET v_counter=v_counter+1;
    OPEN c_ProjectVariables;
    fetch_loop:
    LOOP

    FETCH c_ProjectVariables INTO v_VariableId;
    IF at_end <> 0 THEN LEAVE fetch_loop;
    END IF;


    SELECT COUNT(*) into v_Count
    FROM PROJVAR_XREF
    WHERE variable_id=v_VariableId
    AND project_id <> v_ProjectId;

    IF v_Count > 0 THEN

    DELETE FROM PROJVAR_XREF
    WHERE VARIABLE_ID=v_VariableId
    AND PROJECT_ID=v_ProjectId;

    UPDATE MODEL_MDI
    SET ROOTVAR = NULL
    WHERE ROOTVAR = v_VariableId;


    ELSE



    DELETE FROM PROJVAR_XREF
    WHERE VARIABLE_ID=v_VariableId;

    UPDATE MODEL_MDI
    SET ROOTVAR = NULL
    WHERE ROOTVAR = v_VariableId;

    COMMIT;

    CALL dbuser.PROC_DELETE_VARIABLE(v_VariableId);
    IF at_end <> 3 THEN
    SET del_count = del_count + 1;
    END IF;

    END IF;



    END LOOP fetch_loop;

    CLOSE c_ProjectVariables;
    END WHILE;
    SET counter = del_count ;
    DELETE FROM MODEL_MDI WHERE MODEL_MDI.PROJECT_ID = v_ProjectId ;
    DELETE FROM PROJECT_MDI WHERE PROJECT_MDI.ID = v_ProjectId ;
    COMMIT;

    END p1





    please help me its very urgent

  2. #2
    Join Date
    Feb 2003
    Posts
    9
    CREATE PROCEDURE DB2DBA.proc_name
    (IN in_var INTEGER,
    :
    OUT SQL_CODE INTEGER,
    OUT SQL_STATE CHARACTER(5)
    )
    SPECIFIC DB2DBA.proc_name
    DYNAMIC RESULT SETS 1
    LANGUAGE SQL
    NOT DETERMINISTIC
    CALLED ON NULL INPUT
    MODIFIES SQL DATA

    begin P1

    declare SQLSTATE CHAR(5) DEFAULT '00000';
    declare SQLCODE INT DEFAULT 0;
    :
    :

    declare continue handler for sqlexception, sqlwarning, not found
    begin
    select sqlcode, sqlstate into v_sql_code, v_sql_state from sysibm.sysdummy1;
    end;

    :
    :

    set SQL_CODE = v_sql_code;
    set SQL_STATE = v_sql_state;

    end P1

    put the condition handler in above for your called procedure. then pass back the sqlcode, sqlstate or both to trap the error code in the called procedure.

    you may only want the sqlexception handler, depending on what type of warnings or not found condition could occur in the called procedure.

    if you do not have the right condition handler for the error in the calling procedure, it will probably terminate on the error. not sure if this is what your question is or not.

Posting Permissions

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