Passing Lists as Parameters to SQL Stored Procedures
There are some more effective solutions for passing lists as parameters to stored procedures but here's my simple solution.
BEGIN
DECLARE m_date datetime;
DECLARE sell_id int(11);
DECLARE pos int(11);
DECLARE breaker int(11);
DECLARE product_id varchar(11);
DECLARE product_amount varchar(11);
DECLARE product_size varchar(11);
DECLARE product_color varchar(11);
DECLARE product_price varchar(11);
SET breaker = 0;
SET pos = 0;
SET m_date = now();
INSERT INTO sell_details (sell_desc,modified_date) VALUES(sell_desc,m_date);
SET sell_id = (SELECT LAST_INSERT_ID());
if right(product_id_input,1) <> '-' then
set product_id_input = concat(product_id_input,'-');
end if;
if right(product_amount_input,1) <> '-' then
set product_amount_input = concat(product_amount_input,'-');
end if;
if right(product_size_input,1) <> '-' then
set product_size_input = concat(product_size_input,'-');
end if;
if right(product_color_input,1) <> '-' then
set product_color_input = concat(product_color_input,'-');
end if;
if right(product_price_input,1) <> '-' then
set product_price_input = concat(product_price_input,'-');
end if;
while (breaker < 5000) && (length(product_id_input)>1) && (length(product_amount_input)>1) && (length(product_size_input)>1) && (length(product_color_input)>1) && (length(product_price_input)>1) do
set breaker = breaker + 1;
set pos = INSTR(product_id_input,'-');
set product_id = left(product_id_input,pos-1);
set product_id_input = substring(product_id_input,post+1);
set pos = INSTR(product_amount_input,'-');
set product_amount = left(product_amount_input,pos-1);
set product_amount_input = substring(product_amount_input,post+1);
set pos = INSTR(product_size_input,'-');
set product_size = left(product_size_input,pos-1);
set product_size_input = substring(product_size_input,post+1);
set pos = INSTR(product_color_input,'-');
set product_color = left(product_color_input,pos-1);
set product_color_input = substring(product_color_input,pos+1);
set pos = INSTR(product_price_input,'-');
set product_price = left(product_price_input,pos-1);
set product_price_input = substring(product_price_input,post+1);
INSERT INTO sell_list(sell_id,product_id,product_amount,product_size,product_color,product_price,modified_date) VALUES (sell_id,product_id,product_amount,product_size,product_color,product_price,m_date);
end while;
END
PS1: Do not forget to cast variables to original type in insert statement
PS2: Declare your delimitter as a variable and initialize it.
