STORED PROCEDURES

Kalyan wrote: >I have strange problem in error traping in the stored procedure. >All I want it to return the either isam-err code or sql-error code >for the following statment >SELECT company_name from company >where company_code = <condition> >I would like to know wether any row returned for this query. >In 4gl we can trap by using NOTFOUND class by checking the "status" >variable. I assume that you are trying to trap the problem in the stored procedure? Its not really clear from what you have written. If company_code is defined in the database as a NOT NULL field, do this: define c_code LIKE company.company_code; define c_name LIKE company.company_name; define company_found integer; let company_found = 0; ON EXCEPTION -391 LET company_found = 0; END EXCEPTION WITH RESUME; select company_code, company_name into c_code, c_name..... The stored procedure will fail if the select does not return any values, generating error -391 which is trapped by the exception and processed accordingly. Since the WITH RESUME option is specified for the exception, the procedure will continue from the line after the SELECT. You can test the value of company_found later in the procedure to see if anything was found. Hope this helps Mark Denham BBC London, UK Mark.Denham@bbc.co.uk