Handler for object doesnt exist in Stored procedure

Database
Enthusiast

Handler for object doesnt exist in Stored procedure

Hi All,

 

Could you please help me how to handle object doesnt exists error in stored procedure.

 

Could you please suggest me sample handler code for that?

 

Thanks in advance

 

Regards,

Sreekanth


Accepted Solutions
Junior Apprentice

Re: Handler for object doesnt exist in Stored procedure

Hi Sreekanth,

 

I use the following in one of my SP's:

	lDT_VT1: BEGIN
        /* Trap the TD3807 error for the DROP TABLE command when this is executed first time in a session. */
        DECLARE CONDH_TABLE_NOT_EXIST CONDITION FOR SQLSTATE '42000';
        DECLARE CONTINUE HANDLER FOR CONDH_TABLE_NOT_EXIST
		BEGIN
		  SET vIgnoreMsg = 'ERROR IGNORED '||SQLCODE||'.';
		END;
	    SET vSqlStmt = 'DROP TABLE DBSCTL_VT2;';
	    CALL DBC.SYSEXECSQL(vSqlStmt);
	 END lDT_VT1;

The 'object not exist' (in my case a table - TD 3807 error) translates to SQLSTATE 42000 as per the "Appendix D: SQLSTATE Mappings" in manual "SQL Stored Procedures and Embedded SQL".

 

Start with that and see how you get on.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
1 ACCEPTED SOLUTION
2 REPLIES
Junior Apprentice

Re: Handler for object doesnt exist in Stored procedure

Hi Sreekanth,

 

I use the following in one of my SP's:

	lDT_VT1: BEGIN
        /* Trap the TD3807 error for the DROP TABLE command when this is executed first time in a session. */
        DECLARE CONDH_TABLE_NOT_EXIST CONDITION FOR SQLSTATE '42000';
        DECLARE CONTINUE HANDLER FOR CONDH_TABLE_NOT_EXIST
		BEGIN
		  SET vIgnoreMsg = 'ERROR IGNORED '||SQLCODE||'.';
		END;
	    SET vSqlStmt = 'DROP TABLE DBSCTL_VT2;';
	    CALL DBC.SYSEXECSQL(vSqlStmt);
	 END lDT_VT1;

The 'object not exist' (in my case a table - TD 3807 error) translates to SQLSTATE 42000 as per the "Appendix D: SQLSTATE Mappings" in manual "SQL Stored Procedures and Embedded SQL".

 

Start with that and see how you get on.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Handler for object doesnt exist in Stored procedure

Hi Dave,

 

Thank you so much. It worked.

 

 

Thanks!

Sreekanth.