MySql Procedure values for insert as a variable

Question

I have some trouble with MySql Procedure. I have:

DROP TABLE IF EXISTS `employees2`;
CREATE TABLE `employees2` (
  `LastName` varchar(20) character set utf8 collate utf8_unicode_ci NOT NULL default '',
  `FirstName` varchar(10) character set utf8 collate utf8_unicode_ci NOT NULL default ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

It work's and is OK.

And MySQL Procedure:

DROP PROCEDURE IF EXISTS gen;

DELIMITER $$
CREATE PROCEDURE gen()
    BEGIN
        DECLARE e1 TEXT;
        DECLARE e2 TEXT;
        DECLARE e3 TEXT;
        SET e1 = "Davolio";
        SET e2 = "Nancy";
        SET e3 = "Ron , Deplin";
        insert  into `employees2`(`LastName`,`FirstName`) values ('Nonew','adams');
        insert  into `employees2`(`LastName`,`FirstName`) values (e1,e2);
        insert  into `employees2`(`LastName`,`FirstName`) values (e3);

    END $$
DELIMITER ;

call gen();

I would like to instert into table values from variable e3. "Ron" is for column LastName and "Deplin" is for column FirstName. But i got error: "Error Code: 1136. Column count doesn't match value count at row 1" First and second inserts works fine. How to force the third insert to work ?

Community · Accepted Answer · 2017-05-23 11:56:06Z

0

You're only specifying one value with two columns named in your last insert, hence the unequal row count.

See this post on how to use MySQL to split a string like that got two value entries: https://stackoverflow.com/a/9953163/2812842

edited May 23, 2017 at 11:56

CommunityBot

11 silver badge

answered Nov 15, 2013 at 20:25

scrowler

24.4k10 gold badges67 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

marooned Over a year ago

Yes. But i have: SET e3 = "Ron , Deplin"; And Ron is for LastName column and Deplin is for FirstNamecolumn. I just would like paste this e3 between () in insert query.

marooned Over a year ago

Ok. insert into employees2(LastName,FirstName) values (SUBSTRING_INDEX(e3, ',',1),SUBSTRING_INDEX(e3, ',',-1)); It's works but it's not what i want. When i will have longer list of values this method will be useless.

scrowler Over a year ago

Do you need to split a string like that? The more data you have, the more you should be using separate fields or structured data...

marooned Over a year ago

OK. But what if: insert into employees2(LastName,FirstName,City,Country) values (e1,e2); where my e1 = "Ron , Deplin" and e2 = "NY, USA" I tried change e3 on many ways and it did not pass. For example: e3 = "'Ron' , 'Deplin'" etc @scrowler - maybe i'am not clear. Do you understood what im want to do ?

Collectives™ on Stack Overflow

MySql Procedure values for insert as a variable

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related