Debugging Stored Procedures | Teradata Vantage - Debugging Stored Procedures - Advanced SQL Engine - Teradata Database

SQL Stored Procedures and Embedded SQL

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
xqq1557098602407.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1148
lifecycle
previous
Product Category
Teradata Vantage™

This section provides guidelines for debugging stored procedure-based applications.

The following act as debugging tools:
  • SQL INSERT statement
  • Special purpose stored procedure for logging information

Though no method is perfect, these debugging and testing techniques help minimize bugs.

Comparing Debugging Methods

The following table describes the advantages and disadvantages of these two methods. Evaluate the methods based on your requirements.

Method Advantages Disadvantages
INSERT statement Log entries can be inserted into a user-defined log table and the results can be viewed by querying the log table after execution of the stored procedure. You must disable or remove the INSERT statement(s) from the stored procedure body after debugging, and recompile the procedure.
Debug stored procedure Defines a standard procedure to debug stored procedures. You must disable or remove the CALL statement(s) from the stored procedure body after debugging, and recompile the procedure.

Using INSERT Statements for Debugging

You can use the INSERT statements in the stored procedure to insert any values or text in any table. Consider the following stored procedure definition:

REPLACE PROCEDURE spRow (OUT pRowCount INTEGER)
BEGIN
   DECLARE EXIT HANDLER
   FOR SQLEXCEPTION
   BEGIN
      INSERT ErrorLogTable (’spRow’, :SQLSTATE, :SQLCODE);
   END;
   SET pRowCount = 0;
   FOR vFor AS cName CURSOR FOR
SELECT c1 as a, c2 * 10 as b
FROM ValueTable
   DO
      SET pRowCount = pRowCount + 1;
         INSERT LogTable (pRowCount, vFor.a, vFor.b);
         ...
         ...
         ...
   END FOR;
END;

When the stored procedure is executed, the INSERT statement specified in the FOR statement is executed for each row fetched from the cursor. It inserts the values of row count, column c1, and column c2 of table ValueTable.

If, during the stored procedure execution, an exception or completion condition is raised and it is handled using the declared generic condition handler, the INSERT statement specified within the handle is executed. It inserts the stored procedure name, and values of SQLCODE and SQLSTATE status variables in the ErrorLogTable. You can query this table to identify if there was any exception during stored procedure execution.

Using Debug Stored Procedures

You can write a site-specific or application-specific stored procedure that logs any given text in a standard log table with user-id and so on. Consider the following stored procedure definition:

CREATE PROCEDURE LogDatabase.spDebug (IN spName CHAR(30), IN Text CHAR(255))
BEGIN
   -- Exit in case of any exception.
   DECLARE EXIT HANDLER
   FOR SQLEXCEPTION
    BEGIN
      INSERT ErrorLogTable (spName, :SQLSTATE,
             :SQLCODE);
      END;
   -- Log the text in the DebugTable.
   INSERT INTO LogDatabase.DebugTable(spName,
      USER, CURRENT_DATE, CURRENT_TIME, Text)
END;

The procedure spDebug is created in a predefined database, LogDatabase. The procedure inserts rows in an existing table DebugTable. It accepts two input arguments, a stored procedure name and the text to be logged. The caller needs EXECUTE PROCEDURE privilege on this stored procedure to use it in any other stored procedure.

The following stored procedure calls the LogDatabase.spDebug:

CREATE PROCEDURE spRow (OUT pRowCount INTEGER)
BEGIN
   DECLARE ErrorText CHAR(255) DEFAULT NULL:
   DECLARE EXIT HANDLER
   FOR SQLEXCEPTION
   BEGIN
      SET ErrorText = ’In exception handler ...’ ||        ’SQLCODE: ’ || SQLCODE || ’ SQLSTATE: ’ ||       SQLSTATE;
      CALL LogDatabase.spDebug (’spRow’, ErrorText);
   END;
   SET pRowCount = 0
   FOR vFor AS cName CURSOR FOR
       SELECT c1 as a, c2 * 10 as b
          FROM ValueTable
      DO
      SET pRowCount = pRowCount + 1;
      SET ErrorText = ’RowCount: ’ || pRowCount ||
      ’Values: ’ || vFor.a || ’ ’ || vFor.b;
      CALL LogDatabase.spDebug (’spRow’, ErrorText);
            ...
            ...
            ...
   END FOR;
END;