When I call the procedure and pass the necessary parameters to it, it returns only one record, despite the fact that there are several of them in the database.

delimiter ~ create procedure ShowRange(in MaxRange int) begin declare sId int; declare sName varchar(50); declare sType varchar(50); declare sMaxRange integer; declare countWeapon int; declare done integer default 0; declare ShowRangeCursor Cursor for select model.id,model.name,model.type,characteristics.max_range from model inner join characteristics on model.id = characteristics.model_id where characteristics.max_range = MaxRange limit 1; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1; open ShowRangeCursor; while done = 0 DO fetch ShowRangeCursor into sId,sName,sType,sMaxRange; select count(model.id) into countWeapon from model inner join characteristics on model.id = characteristics.model_id where characteristics.max_range = MaxRange; if(countWeapon >0) then select sId as 'ID', sName as 'Name',sType as 'Type',sMaxRange as 'range'; else select 'В таблице не обнаружено записей' as 'Помилка'; end if; end while; close ShowRangeCursor; end~
  • You have a cursor there ... The procedure returns the last one processed by the cursor. Could it be that the last record processed by the cursor is given only one? - cyadvert
  • At you in select in the cursor limit 1 so she always no more than one record returns. And it is not clear why there is a procedure here at all, I don’t see anything in it that cannot be selected with one request - Mike
  • @Mike This limit does not solve anything. Why procedure? Because at the moment I am going through practice and so it is necessary. - pride
  • @makintosh How not to solve anything. the cycle is executed only once, and its select is select sId as 'ID', sName as 'Name',sType as 'Type',sMaxRange as 'range'; clearly choosing one line - Mike
  • And by the way, if the cycle runs N times, it will return not N records, but N sets of one record, which in most cases is not the same. In order for the procedure to return a single set of N records, they must all be selected simultaneously, with one request - Mike

1 answer 1

Reply from comments from Mike

You have a SELECT in the LIMIT 1 cursor so that it always returns no more than one record. In addition, if the cycle runs N times, it will return not N records, but N sets of one record, which in most cases is not the same. In order for the procedure to return a single set of N records, they should all be selected simultaneously, in one request.