Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2004
    Posts
    57

    Unanswered: Can I catch the error in a stored procedure.

    I have some insert procedures like the folowing (written in SQL Server):

    create procedure Insert_Item (@1, @2 etc)
    as

    declare @error int

    insert into Item (field1, field2, ...)
    values (@1, @2, ...)
    select @error = @@error

    if @error <> 0
    exec Handle_errors (Object_Name, Table_Name, etc)

    IS it possible to catch the error in Db2 in a similar manner?
    Thanks for your support.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Yes,
    Look in the "SQL Refernece" manual under "Compound Statement (Procedure)"
    for the correct syntax. Look particularly at the handler-declaration section.

    HTH

    Andy

  3. #3
    Join Date
    Jun 2004
    Posts
    57

    Catching and handling the sqlstate value.

    Sorry if I wasn't very precise.
    I lost all my yesterday tryiing to resolve this problem.
    I have a custom procedure handle_errors. I have a parameter in this procedure who stands for the original error value (which correspond to @@error in sql server and to sqlstate in DB2).
    I would like to catch the sqlstate value and in the handle to call my custom procedure having sqlstate as parameter, but I couldn't.
    I sqw that I can handle the errors one by one (FK violation errors, unique violations errors, each of them having a sql state). But I was wondering if it is not possible to catch the sql state value and to have a single handle for sql state and in this handle to get sql state as parameter.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I believe that you want something like this:

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
    GET DIAGNOSTICS EXCEPTION 1 MESSAGE_TEXT_OUT = MESSAGE_TEXT;
    SELECT SQLSTATE, SQLCODE INTO SQLSTATE_OUT, SQLCODE_OUT
    FROM SYSIBM.SYSDUMMY1;

    ROLLBACK;
    END;

    SQLEXCEPTION will catch all SQLSTATEs starting with "00", "01", or "02"

    HTH

    Andy

Posting Permissions

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