CODEWITHSUNDEEP
mysqlsqlphpmyadminmysql-workbench
newman
newman

Reputation: 424

Issue with case statement inside stored procedure

I am writting and update to my database, which i want to import but i do not know what is wrong with my code. MySQL Workbench and phpMyAdmin return me a syntax error. It is just an example case statement. Can anyone help me? 

SET @company_code = 0;
SELECT @company_code := LOWER(`value`) FROM `settings` WHERE `setting_name` = 'COMPANY_CODE';
CREATE PROCEDURE p(in  code varchar(200))
BEGIN 
CASE code  
    WHEN 'first_company' THEN SELECT code;
    ELSE SELECT '1';
END CASE
END;

CALL p(@company_code);

Can i do that without creating the procedure?

Upvotes: 1

Views: 754

Answers (2)

Salman Arshad
Salman Arshad

Reputation: 272236

  1. You are missing a semi-colon after END
  2. You need to use a delimiter other than ; in order to create procedure

See Defining Stored Programs for explanation.

DELIMITER foo

CREATE PROCEDURE p(IN code VARCHAR(200))
BEGIN
  CASE code
    WHEN 'first_company' THEN INSERT INTO dest_first SELECT code, * FROM srctable;
    ELSE                      INSERT INTO dest_other SELECT '1',  * FROM srctable;
  END CASE;
END;
foo

DELIMITER ;

SET @company_code = 0;
SELECT @company_code := LOWER(value) FROM settings WHERE setting_name = 'COMPANY_CODE';
CALL p(@company_code);

Upvotes: 1

Mittal Patel
Mittal Patel

Reputation: 2762

As per our discussion above, If "settings" table has the record with company_code = "first_company" then you want to insert the record then it can be achieved by this way

INSERT INTO colourmst (CODE, Column1) 
SELECT LOWER(`value`) , 'XYZ'
FROM `settings` WHERE `setting_name` = 'COMPANY_CODE'

Upvotes: 0

Related Questions