Mysql if not exists gives my an error in a stored-procedure

Question

I have the following stored procedure in phpmyadmin using mysql:

CREATE DEFINER=`user`@`localhost` PROCEDURE `set_address`(IN `patient_street` VARCHAR(128), IN 
`patient_city` VARCHAR(45), IN `patient_post_code` VARCHAR(45), IN `patient_state_or_province` VARCHAR(45), IN `patient_country` VARCHAR(45)) 
NOT DETERMINISTIC NO SQL SQL SECURITY 
DEFINER 
BEGIN 
IF NOT EXISTS (SELECT a.address_id FROM address as a where a.street = patient_street and a.city = patient_city and a.post_code = patient_post_code and a.country = patient_country) 
THEN 
BEGIN 
INSERT INTO address (street, city, post_code, state_or_province, country) VALUES (patient_street, patient_city, patient_post_code, patient_state_or_province, patient_country); 
SELECT LAST_INSERT_ID(); 
END; 
ELSE 
BEGIN 
SELECT a.address_id FROM address as a where a.street = patient_street and a.city = patient_city and a.post_code = patient_post_code and a.country = patient_country 
END; 
END IF; 
END;

However, I got 2 errors in IF NOT EXISTS:

Unrecognized keyword. (near IF NOT EXISTS)
Unexpected token. (near()

Every if must have a then and an end if. Your insert statement doesn't make a lot of sense either , you cannot mix insert...values with a select like this.And mysql does not require a begin end block in an if end if block. — P.Salmon
– P.Salmon, Commented Nov 21, 2018 at 15:30
@P.Salmon I want to check firstly whether the address is already stored in my db otherwise store it and get it's corresponding id. — zinon
– zinon, Commented Nov 21, 2018 at 15:32
You probably need to terminate the insert..values statement. — P.Salmon
– P.Salmon, Commented Nov 21, 2018 at 15:34
You really should remove the requirement to terminate statements - you are missing ; after end if and all end blocks also you haven't shown that you are resetting delimiters before and after procedure creation code (which you also haven't shown). — P.Salmon
– P.Salmon, Commented Nov 21, 2018 at 15:47

spencer7593 · Accepted Answer · 2018-11-21 16:34:12Z

Personally, if I had a requirement to write a procedure that performed the specified operations, and returned a resultset, I'd write it like this:

DELIMITER $$

CREATE DEFINER=`user`@`localhost` PROCEDURE `set_address`
(IN `patient_street`            VARCHAR(128)
,IN `patient_city`              VARCHAR(45)
,IN `patient_post_code`         VARCHAR(45)
,IN `patient_state_or_province` VARCHAR(45)
,IN `patient_country`           VARCHAR(45)
)
NOT DETERMINISTIC SQL SECURITY DEFINER
BEGIN
  DECLARE li_address_id BIGINT DEFAULT NULL;
  -- check for existing row and get address_id
  SELECT a.address_id
    INTO li_address_id
    FROM address a
   WHERE a.street    = patient_street
     AND a.city      = patient_city
     AND a.post_code = patient_post_code
     AND a.country   = patient_country
   LIMIT 1 ;
  -- if we didn't find a matching row
  IF li_address_id IS NULL THEN
    -- add a row and get the new address_id
    INSERT INTO address (street, city, post_code, state_or_province, country) VALUES
    (patient_street, patient_city, patient_post_code, patient_state_or_province, patient_country);
    SELECT LAST_INSERT_ID() INTO li_address_id;
  END IF;

  -- return address_id (either found existing row, or newly added row) as a resultset
  SELECT li_address_id AS address_id;
END$$

DELIMITER ;

If a row exists, we don't need to run two SELECT statements. We can do the check for the row AND get the address_id with a single SELECT.

If we didn't get a matching row, then we insert a row, and retrieve the auto-increment id.

In either case (found row, or added row), return the address_id as a resultset. Again, we can do that with a single SELECT statement, rather than two different statements.

To me, it makes more sense to limit the number of places we are returning resultsets, and limit the number of times we query the database.

Collectives™ on Stack Overflow

Mysql if not exists gives my an error in a stored-procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related