CODEWITHSUNDEEP
mysqlsql
tfirinci
tfirinci

Reputation: 159

my script doesn't run although i tried different cases

Below you can see my script , when try to run CREATE PROCEDURE, it always gives syntax error , please let me know what is wrong?

I triead to delete ";" in front of END IF on create procedure and also I tried to change position of END IF 

CREATE TABLE IF NOT EXISTS parts (
    part_no VARCHAR(18) PRIMARY KEY,
    description VARCHAR(40),
    cost DECIMAL(10 , 2 ) NOT NULL,
    price DECIMAL(10,2) NOT NULL
);

CREATE PROCEDURE `check_parts`(IN cost DECIMAL(10,2), IN price DECIMAL(10,2))
BEGIN
    IF cost < 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'check constraint on parts.cost failed';
    END IF;

    IF price < 0 THEN
 SIGNAL SQLSTATE '45001'
 SET MESSAGE_TEXT = 'check constraint on parts.price failed';
    END IF;

    IF price < cost THEN
 SIGNAL SQLSTATE '45002'
            SET MESSAGE_TEXT = 'check constraint on parts.price & parts.cost failed';
    END IF;

END

Upvotes: 0

Views: 56

Answers (1)

Daniel E.
Daniel E.

Reputation: 2480

You just have to change the delimeters, because in the procedure written, it will stop at the 1st ";"

DELIMITER //
CREATE PROCEDURE `check_parts`(IN cost DECIMAL(10,2), IN price DECIMAL(10,2))
BEGIN
    IF cost < 0 THEN
        SIGNAL SQLSTATE '45000'
          SET MESSAGE_TEXT = 'check constraint on parts.cost failed';
    END IF;

    IF price < 0 THEN
      SIGNAL SQLSTATE '45001'
         SET MESSAGE_TEXT = 'check constraint on parts.price failed';
    END IF;

    IF price < cost THEN
       SIGNAL SQLSTATE '45002'
         SET MESSAGE_TEXT = 'check constraint on parts.price & parts.cost failed';
    END IF;

END //
DELIMITER ;

Should work.

Upvotes: 2

Related Questions