MySql stored procedure syntax error?

Question

I am using MySql and writing the code below as part of my stored procedure:

  SET @label_id_configuration = 0;
  IF NOT (SELECT count(1) FROM `system_labels` WHERE  `name` = 'configuration') THEN
    SELECT @label_id_configuration := Max(`label_id`) + 1 FROM   `system_labels`;
    INSERT INTO `system_labels` (`label_id`, `name`) VALUES (@label_id_configuration, 'Configuration');
  END IF;

It runs fine on my local system. But on the build server, it fails. The rest of the stored procedure runs fine, which means we don't have permission, database, table existence problems.

Could anyone give me a hint to troubleshoot the error message received on the server?

Error on or near line 151: error in SQL script data_update.sql: @label_id_configuration:=`label_id` 6

why don't you try INSERT INTO system_labels (label_id, name) SELECT Max(label_id) + 1, 'Configuration' FROM system_labels. — Krish
– Krish, Commented Dec 19, 2014 at 14:35

Bohemian · Accepted Answer · 2014-12-19 14:46:03Z

1

I don't know why it's erroring, but I can see how you can eradicate the use of that variable altogether (given the code shown):

IF NOT EXISTS(SELECT * FROM `system_labels` WHERE `name` = 'configuration') THEN
    INSERT INTO `system_labels` (`label_id`, `name`)
    SELECT Max(`label_id`) + 1, 'Configuration' FROM `system_labels`
END IF;

answered Dec 19, 2014 at 14:46

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

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

Comments

Believe2014 · Accepted Answer · 2014-12-22 19:30:43Z

0

Thanks all for your help.

After lots of investigation with no luck. I decided to use Insert Ignore and Replace instead of the script above.

answered Dec 22, 2014 at 19:30

Believe2014

3,9943 gold badges28 silver badges46 bronze badges

Collectives™ on Stack Overflow

MySql stored procedure syntax error?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related