MySQL Forums
Forum List  »  Stored Procedures

Re: Stored procedure
Posted by: Peter Brawley
Date: December 19, 2014 02:49PM

Two big problems:

1. CONCAT('SELECT `id_book` into ',var1,' ... would select into the value of var1 rather than into var1 irtself. Not what you wanted.

As a general rule, when you are preparing queries in this way, stub out the resulting query for debugging purposes so you can see what your code is generating. When ypu've proved the sproc works, comment out the stub.

2. IAC PREPARE needs user vars for such assignments.

So we ahve ...

drop procedure if exists someproc;
delimiter go
create procedure someproc(in startindex int, in countnum int, out var1 int) reads sql data 
begin 
  set @idval=-1;  
  set @assd = concat( 'select id_book into @idval from suitcase_booking limit ', startindex, ',', countnum ); 
  -- select @assd;
  prepare zxc from @assd; 
  execute zxc; 
  drop prepare zxc;
  set var1=@idval;
end;
go
delimiter ;
set @ret=-1;
call someproc(0,1,@ret);
select @ret;

Options: ReplyQuote


Subject
Views
Written By
Posted
2930
December 19, 2014 08:37AM
Re: Stored procedure
1135
December 19, 2014 02:49PM
1096
December 22, 2014 03:54AM


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.