To pass LIMIT as parameters to MySQL stored procedure, because LIMIT cannot be parametrized in MySQL stored procedures, we have to do it indirectly using prepared statements.
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;
END$$
Comments
Post a Comment