Wsrtjtyk

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:

1. Unrecognized keyword. (near IF NOT EXISTS)


2. Unexpected token. (near()

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.