How to return error message from nested stored procedure/trigger

[former_member1021736]

Hi,

I'm having a mental block on how to do this....

I have the following scenario (I'm using Watcom dialect):  I call a "sp_transactionPost" stored procedure, which then calls a "sp_transactionRowPost" which among other things INSERTs rows into a "inventoryKardex" table that has a trigger.

From the client application (Clarion for Windows) all I do is call the top "sp_transactionPost" stored procedure, and I would like to receive an informative error should the process fails.  Now, the process could fail at the top procedure (for example the "transaction date is invalid"), or could fail at the row posting procedure (for example the "item has been changed to a status of inactive"), or could fail at the "inventorykardex" trigger (for example because some other user had a row locked).

I imagine that I have to somehow report the error back to the TOP stored procedure ("sp_transactionPost")...... but how?

Thanks,

Edgard

Here I have attached the VERY ROUGH skeleton of what I want to do (hopefully it is understandable):

ALTER PROCEDURE "DBA"."sp_PostTransaction"(IN pIDLoc INT,IN pIDSeq INT,IN pDir INT)

-- ACTIVE Version 01.20 (Abril, 2013)

BEGIN

   DECLARE @sqlcode INTEGER;

   DECLARE @sqlstate VARCHAR(5);

   DECLARE @errormsg VARCHAR(200);

   DECLARE xLocalErrorMsg VARCHAR(200);

   SET TEMPORARY OPTION Chained = 'On';        -- Make sure we ARE in chained mode..

   SET TEMPORARY OPTION Isolation_Level = 3;   -- Serializable

   FOR LOOP_z AS CURSOR_z

      CURSOR FOR SELECT

              iTranR.IDLoc                      AS iTranR_IDLoc,

              iTranR.IDSeq                      AS iTranR_IDSeq,

              iTranR.IDLinea                    AS iTranR_IDLinea

      FROM iTransRow AS iTranR

      WHERE iTranR.idLoc=pIDLoc AND iTranR.idSeq=pIDSeq AND iTranR.Item IS NOT NULL AND iTranR.Cantidad <> 0

            AND (iTranR.Status & 64) = (CASE pDir WHEN -1 THEN 64 WHEN 1 THEN 0 END)

      DO

          CALL sp_postTransactionRow(iTranR_IDLoc,iTranR_IDSeq,iTranR_IDLinea,pDir);

   END FOR;

   -- Normal termination if no errors...

   COMMIT;

   SET TEMPORARY OPTION Chained = null;           -- Revert chained mode...

   SET TEMPORARY OPTION Isolation_Level = null;   --

  EXCEPTION

        WHEN OTHERS THEN

            SELECT SQLCODE, SQLSTATE, ERRORMSG()

                   INTO @sqlcode,@sqlstate,@errormsg;

            ROLLBACK;

            SET xLocalErrorMsg = 'Could not post the transaction.'

            RAISERROR 99999

                            '%1!, SQLCODE = %1!, SQLSTATE = %2!, ERRORMSG() = %3!',

                            xLocalErrorMsg,@sqlcode,@sqlstate,@errormsg;    

            SET TEMPORARY OPTION Chained = null;           --

            SET TEMPORARY OPTION Isolation_Level = null;   --

END

ALTER PROCEDURE "DBA"."sp_PostTransactionRow"(IN pIDLoc INT,IN pIDSeq INT,IN pIDLinea INT,IN pDir INT)

BEGIN

   -- Exceptions....

   DECLARE transPosted          EXCEPTION FOR SQLSTATE VALUE 'POST1';

   SET TEMPORARY OPTION Chained = 'On';         -- Chained mode..

   SET TEMPORARY OPTION Isolation_Level = 3;    -- Serializable

   -- Check status of header...

   SET xStatus = (SELECT Status from iTrans WHERE IDLoc=pIDLoc AND IDSeq=pIDSeq);

   IF xStatus & 64 <> 0  THEN                   -- La transacción está actualizada...NO PODEMOS SEGUIR....

      SIGNAL transPosted;

   END IF;

   -- Insert row into KARDEX...

   FOR LOOP_z AS CURSOR_z

      CURSOR FOR SELECT       

              ISNULL(iTranR.Item,0)       AS iTranR_Item,

              ISNULL(iTranR.Qty,0)        AS iTranR_Qty,

              etc...

      FROM iTransRow AS iTranR

          JOIN iTrans       AS iTran  ON iTran.idLoc=iTranR.idLoc AND iTran.idSeq=iTranR.idSeq

          JOIN iCodigoTrans AS iCTr ON iCTr.Bodega=iTran.Bodega AND iCTr.CodigoTrans=iTran.CodigoTrans

          LEFT OUTER JOIN dOrdenes as dOrd ON dOrd.idOrden = iTran.NoOrden

          WHERE iTranR.idLoc=pIDLoc AND iTranR.idSeq=pIDSeq AND iTranR.idLinea=pIDLinea

                AND iTranR.Item IS NOT NULL AND iTranR.Cantidad <> 0

                AND iCTr.Inv <= 1 AND (iTranR.Status & 64) = (CASE pDir WHEN -1 THEN 64 WHEN 1 THEN 0 END)

      DO

         INSERT INTO iInvKardex (Warehouse,xxxx,xxx,xxx,xxx)

                VALUES (xxxxx,xxxxxx,xxxxxx,xxxxx)

       ....

    -- If everything is ok

   COMMIT;

    SET TEMPORARY OPTION Chained = null;           --

   SET TEMPORARY OPTION Isolation_Level = null;   --

   EXCEPTION

        WHEN transPosted THEN

            SET ErrorCodeMsg = ERRORMSG();

            ROLLBACK;

            RAISERROR 50000 'transaction was already posted';

            SET TEMPORARY OPTION Chained = null;

            SET TEMPORARY OPTION Isolation_Level = null;   --

        WHEN OTHERS THEN

            SET ErrorCodeMsg = ERRORMSG();

            ROLLBACK;

            RAISERROR 50000 'some error ocurred';

            SET TEMPORARY OPTION Chained = null;         

            SET TEMPORARY OPTION Isolation_Level = null;   --

END

ALTER TRIGGER "process_iInvKardex" AFTER INSERT, UPDATE, DELETE

ORDER 1 ON iInvKardex

REFERENCING OLD AS o

            NEW AS n

FOR EACH ROW /* WHEN( search_condition ) */

BEGIN

  IF DELETING THEN

     IF o.historyFlag = 0 Then

        RAISERROR 'Cannot delete from this table non-history rows...';

     END IF;

  ELSE

     ..... do a bunch of stuff.....

     how to report the error back to the client application....

    END IF;  

END

[jeff_albion]

Hi Edgard,

There is an example of this use-case in the documentation: http://dcx.sybase.com/index.html#sa160/en/dbusage/ptewnc.html - does this example help you with the format for raising exceptions in nested stored procedures?

If not, maybe you can post a full sample of the stored procedure code of what you have tried and what behaviour/error you're now seeing?

Regards,

Jeff Albion

SAP Active Global Support