Not getting output via my stored procedure

Question

I have written a stored procedure to retrieve set of data from a table.

Data is available in table for the input given to stored procedure but it is returning nothing.

Below is my stored procedure.

CREATE PROCEDURE  admin_lookup_qry (p_apn char(10), p_cbsa_code integer)
    RETURNING varchar(50);
    --Client Number 526586
    --Client Name Direct Connect Inc.
    --Client Abbrev dircon
    --Tables admin_lookup

        DEFINE ringto_num char(10);         
        DEFINE overflow_flag char(3);
        DEFINE overflow_ring_to_num char(10);
        DEFINE num_rings_overflow integer;
        DEFINE cnt_ringto_num integer;
        DEFINE return_string char(50);
        DEFINE sql_err integer;
        DEFINE isam_err integer;        

        -- Begin Error Handling
        ON EXCEPTION SET sql_err, isam_err
             LET return_string = 'ERROR'||','||sql_err||','||isam_err;
             RETURN  return_string;
        END EXCEPTION

        SET DEBUG FILE TO '/tmp/trace/admin_lookup_qry.trace';
        TRACE ON;   
        -- Initialize Variables
        LET return_string = "NONE";
        LET cnt_ringto_num = 0;
        LET ringto_num = " ";
        LET overflow_flag = " ";
        LET num_rings_overflow = 0;
        LET overflow_ring_to_num = " ";

           SELECT count(ringto_number)
           INTO cnt_ringto_num
           FROM admin_lookup
           WHERE apn = p_apn
           AND cbsa_code = p_cbsa_code;            

           IF cnt_ringto_num > 0 THEN
            SELECT ringto_number, overflow_busy_flag, no_of_rings_to_overflow,overflow_ringto_number
            INTO ringto_num, overflow_flag,num_rings_overflow,overflow_ring_to_num
            FROM admin_lookup
            WHERE apn = p_apn
            AND cbsa_code = p_cbsa_code;        

            LET return_string = 'OK'||','||ringto_num||','||TRIM(overflow_flag)||','||num_rings_overflow||','||overflow_ring_to_num;
            RETURN return_string;
           ELSE
              RETURN return_string;
           END IF;          

    END PROCEDURE;

Below is the table schema,

create table admin_lookup
     (
       toll_free_number char(10),
       apn char(10),
       client_number integer,
       cbsa_code char(6),
       ringto_number char(10),
       overflow_busy_flag char(3),
       no_of_rings_to_overflow smallint,
       overflow_ringto_number char(10),
       primary key (apn,cbsa_code)
      );

here is the sample data ,

toll_free_number    8885337800
apn                 8117472815
client_number       10015
cbsa_code           36540
ringto_number       4022096303
overflow_busy_flag  No
no_of_rings_to_ov+
overflow_ringto_n+

When i pass valid apn and cbsa_code as input it returns as 1 rows retrieved but no data is displaying on the screen. But, if i pass invalid parameter it returns as NONE.

Not getting output via my stored procedure

Answers (1)

Related Questions