mysql-prepare-statement-in-limit-clause-example.sql

delimiter $$
CREATE DEFINER=`i88ca`@`192.168.%` PROCEDURE `general_split_list`(in v_listid int, in v_listid1 int, in v_listid2 int, in percentage int)
BEGIN
-- split v_listid into v_listid1 and v_listid2, the ratio is the percentage.set @v_listid=v_listid;
select l.totalcontacts into @total from lists l where l.listid=@v_listid;
select @total*percentage/100 into @count;
set @v_listid1=v_listid1;
set @v_listid2=v_listid2;
set @count2=@total-@count;
PREPARE i88_ca FROM 'insert low_priority ignore into sharedlist(listid, contactid) select ?, sl.contactid  from sharedlist sl join contacts c on c.contactid=sl.contactid where sl.listid=? order by c.email limit ?';
EXECUTE i88_ca USING @v_listid1,@v_listid,@count;

PREPARE STMT FROM 'insert low_priority ignore into sharedlist(listid, contactid) select ?, sl.contactid  from sharedlist sl join contacts c on c.contactid=sl.contactid where sl.listid=? order by c.email limit ?,?';
EXECUTE STMT USING @v_listid2,@v_listid,@count, @count2;

END$$