Error handling in Teradata Procedures

[scarletAni]

I have the following procedure and want to introduce error handling.

Replace procedure appl_distrib_chnl_drv_01.pr_junk(OUT msg_txt VARCHAR(100))
lbl_sls_out_dtl :
begin
/* Variables for detecting SQL errors */
DECLARE sql_cd INTEGER;
DECLARE sql_st CHAR(5);

/* Declare continue on to next statement SQL standard error variables */
DECLARE continue handler for sqlexception
BEGIN
SET sql_cd = SQLCODE; /* Capture this data */
SET sql_st = SQLSTATE;
END;
/* Initialize sql standard error variables */
SET sql_cd = 0;
SET sql_st = ' ';

insert into dwtmp1.junk values(2,'a');

IF SQLCODE <> 0 THEN
SET msg_txt = ' SQLCODE ' || sql_cd || ' SQLSTATE ' || sql_st
;
LEAVE lbl_sls_out_dtl;
END IF;

Set msg_txt = 'success';
end lbl_sls_out_dtl;

I call the procedure as

Call appl_distrib_chnl_drv_01.pr_junk(msg_txt);

There must be an error indicated as the user does not have permission to insert data into the dwtmp1.junk table.
But I don't get any error message, nor does theLeave statement get the control out of hte procedure. It continues and I get the output message &quot;Success&quot;

But if I change the error handling to
IF sql_cd <> 0 THEN
SET msg_txt = ' SQLCODE ' || sql_cd || ' SQLSTATE ' || sql_st
;
LEAVE lbl_sls_out_dtl;
END IF;


I get the proper error indicated. How do I get the error message ? Note that the change I have made is to change the SQLCODE in the IF statement to the variable sql_cd.

Could you also explain as to how this error handling works ?

 

[dnoeth]

There is an error, but as it's a CONTINUE handler, the script continues and SQLSTATE and SQLCODE are reset after the continue handler finishes (or the first sql statement within the handler!), so you have to compare to sql_cd instead of SQLCODE.

If you want to exit, why don't you declare an EXIT handler?
You should always exit for an unknown exception, only specific errors should be treated by a CONTINUE handler.

Dieter

 

[scarletAni]

Dieter,
If I use sql_cd it works and exits correctly. This is because of the LEAVE statement within the IF block.

Wanted to know more about how the Error handling actually works.

 

[dnoeth]

1. There's an error.

2. The handler is called.

3. When the handler completes, the status variables are reset to
SQLSTATE --> '00000'
SQLCODE --> 0
ACTIVITY_COUNT --> 0

4. SQLCODE <> 0 returns FALSE

SQL Ref. Manual, Vol 6, Chapter 9: Condition Handling

Dieter