MySQL Forums
Forum List  »  Stored Procedures

Need help for store procedure output
Posted by: ATANU GOLUI
Date: March 07, 2013 03:38AM

Dear All,

I am extremely new dba with this database.Now creating one SP which is given below.
Compilation is successful.On running mode its only print upto 7.After that is showing :-
ERROR 1329 (02000): No data - zero rows fetched, selected, or processed

That means from

FETCH new_stnd_id INTO n_stnd_id;

line onwards its not working.But not find out the problem.Please help.Entire SP is given below.

DELIMITER //



CREATE PROCEDURE flt_suspended
(
IN ad_hoc_flt_id int(11),
IN eta datetime,
IN actual_w_span decimal(6,2),
IN terminal_no varchar(3),
IN a_iata_cd_inpt varchar(3),
OUT id int
)


BEGIN
/* DECLARE st_stat CURSOR FOR select stnd_stat from daily_stnd_schd
where stnd_stat='SUSPENDED'; */
DECLARE s_schd_id int(11);
DECLARE a_flt_id int(11);
DECLARE s_no varchar(3);
DECLARE t_no varchar(3);
DECLARE a_iata_cd varchar(3);
DECLARE p_st_tm datetime;
DECLARE p_end_tm datetime;
DECLARE s_stat varchar(10);
DECLARE c_by varchar(50);
DECLARE c_on datetime;
DECLARE st_s varchar(10);
DECLARE v int(1);
DECLARE n_stnd_id varchar(3);
DECLARE p_st_time datetime;
DECLARE p_e_t datetime;
DECLARE tm_diff int(3);
DECLARE s_n CURSOR FOR select a.stnd_no from ref_stnd a where
terminal_no=a.trmnl_no and actual_w_span<=a.max_wng_spn;
DECLARE t_diff CURSOR FOR select TIMESTAMPDIFF(MINUTE,c.pln_st_tm,eta) from daily_stnd_schd c where TO_DAYS(c.pln_st_tm)=TO_DAYS(eta);
DECLARE new_stnd_id CURSOR FOR select b.stnd_no from daily_stnd_schd b where s_no=b.stnd_no and (b.trans_complt IS NULL OR b.trans_complt='C');

select 1;

OPEN s_n;
select 2;
OPEN t_diff;
select 3;
OPEN new_stnd_id;
select 4;
read_loop: LOOP
select 5;
FETCH s_n INTO s_no;
select 6;
FETCH t_diff INTO tm_diff;
select 7;
FETCH new_stnd_id INTO n_stnd_id;
select 8;
/* OPEN st_stat;
FETCH st_stat INTO st_s; */
select 9;


if (tm_diff<30) then
select 10;
/* select b.stnd_no INTO n_stnd_id from daily_stnd_schd b where s_no=b.stnd_no and (b.trans_complt IS NULL OR b.trans_complt='C'); */

if (n_stnd_id!='0') then
select 11;

select ad_hoc_flt_id;
select eta;
select a_iata_cd_inpt;
SET id=1;
select id;
select s_no;
select tm_diff;
select n_stnd_id;
end if;
end if;
END LOOP;
CLOSE new_stnd_id;
CLOSE s_n;
CLOSE t_diff;
end //
DELIMITER ;



Edited 1 time(s). Last edit at 03/07/2013 03:40AM by ATANU GOLUI.

Options: ReplyQuote


Subject
Views
Written By
Posted
Need help for store procedure output
2133
March 07, 2013 03:38AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.