I want to insert multiple customers licenses into my table by using a function I create. I parse in a string separated by ,. For example:
SELECT INSERT_CUS(1, '111,222,333');
This should yield a licenses table like:
| cus_id | License |
|---|---|
| 1 | 111 |
| 1 | 222 |
| 1 | 333 |
CREATE TABLE customers(
cus_id int not null
);
CREATE TABLE licenses(
cus_id int not null, foreign key (cus_id) references customers(cus_id),
license int not null
);
However, it errors out because I am trying to insert a string into a int column.
DELIMITER //
CREATE FUNCTION INSERT_CUS(P_CUS_ID INT, P_LICENSES VARCHAR(512))
RETURNS INT UNSIGNED
BEGIN
INSERT INTO licenses (cus_id, license) VALUES (P_CUS_ID, P_LICENSES);
RETURN 1;
END; //
DELIMITER ;