Exception handling in Stored procedure

General
Teradata Employee

Exception handling in Stored procedure

Hi,

I have a stored procedure which gets a SQL query as a parmeter and then execute that query using DBC.SYSEXECSQL. I have also put an exception block in the SP, which is given below:

 

DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        INSERT....
    END;

 

My requirement is to catch any SQL exception, which may occur while executing the query. Which means that if the user has sent wrong query to this SP, then instead of failing and quitting, SP should go to exception block and run the INSERT given over there.

 

I have noticed that its working as intended but the exception block is not catching the syntax errors. Which means that whenever DBC.SYSEXECSQL get a query where the syntax is not correct, it thorws an error and SP quits rather than going into the exception block and running the INSERT. I have noticed this specifically for SQLCode 3707, which is use for syntax errors.

 

Can anyone tell me why is it not catching the syntax isssue? And how to handle it


Accepted Solutions
Teradata Employee

Re: Exception handling in Stored procedure

Hi dnoeth,

Thanks for your response.

 

I have found the issue. The INSERT INTO... statement in my exception blocks had an error itself, thats the reason that instead of running the exception handling block and quitting the SP gracefully, it was not catching the error 3707.

1 ACCEPTED SOLUTION
5 REPLIES
Highlighted
Junior Contributor

Re: Exception handling in Stored procedure

Can you show the actual source code, the handler you showed will catch any error including 3707 for dynamic SQL.

 

To define the handler only for the dynamic SQL step you can a compound statement, i.e. a BEGIN/END-block:

REPLCE PROCEDURE ...
BEGIN
...
   BEGIN
      DECLARE EXIT HANDLER FOR SQLEXCEPTION
      BEGIN
         INSERT....
      END;
      CALL DBC.SYSEXCSQL...
      --- better use EXECUTE IMMEDIATE instead
   END

 

Teradata Employee

Re: Exception handling in Stored procedure

Hi dnoeth,

Thanks for your response. Following is my code of the SP:

REPLACE PROCEDURE DYNAMIC_SQL_EXE (IN PAR_SQL_EXE VARCHAR(6000), OUT PAR_EXE_STATUS CHAR(1))
BEGIN
	DECLARE VAR_STEP VARCHAR(6000);
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		INSERT INTO....
		SET PAR_EXE_STATUS = '0';
	END;
	CALL DBC.SYSEXECSQL(PAR_SQL_EXE);
	SET PAR_EXE_STATUS = '1';
END;

I have also tried changing the BEGIN/END block but its not catching the syntax errors either. Below is the code:

REPLACE PROCEDURE DYNAMIC_SQL_EXE (IN PAR_SQL_EXE VARCHAR(6000), OUT PAR_EXE_STATUS CHAR(1))
BEGIN
	DECLARE VAR_STEP VARCHAR(6000);
	BEGIN
		DECLARE EXIT HANDLER FOR SQLEXCEPTION
		BEGIN
			INSERT INTO....
			SET PAR_EXE_STATUS = '0';
		END;
	CALL DBC.SYSEXECSQL(PAR_SQL_EXE);
	SET PAR_EXE_STATUS = '1';
	END;
END;

Regards,

Waqas



Teradata Employee

Re: Exception handling in Stored procedure

Any idea how to resolve this issue?

Junior Contributor

Re: Exception handling in Stored procedure

REPLACE PROCEDURE DYNAMIC_SQL_EXE (IN PAR_SQL_EXE VARCHAR(6000), OUT PAR_EXE_STATUS CHAR(1), OUT errmsg VARCHAR(200))
BEGIN
	DECLARE VAR_STEP VARCHAR(6000);
	DECLARE EXIT HANDLER FOR SqlException
	BEGIN
      GET DIAGNOSTICS EXCEPTION 1 errmsg = Message_Text;
		SET PAR_EXE_STATUS = '0';
	END;
	CALL DBC.SYSEXECSQL(PAR_SQL_EXE);
	SET PAR_EXE_STATUS = '1';
END;

CALL DYNAMIC_SQL_EXE('wrong syntax', a, b);
-- returns -- PAR_EXE_STATUS errmsg -- 0 Syntax error: expected something between the beginning of the request and the word 'wrong'.
Teradata Employee

Re: Exception handling in Stored procedure

Hi dnoeth,

Thanks for your response.

 

I have found the issue. The INSERT INTO... statement in my exception blocks had an error itself, thats the reason that instead of running the exception handling block and quitting the SP gracefully, it was not catching the error 3707.