I'm wrote this mysql procedure for inserting customer billing details into an ecommerce database.
If i run this in phpmyadmin, it throws errors for any semicolons. I removed the semicolons but it gives the following error
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE addr_id INT UNSIGNED ... at line 4
I've never worked with procedures before but i double checked the syntax and can't figure out the error.
DELIMITER //
CREATE PROCEDURE addCustomer(email VARCHAR(60), status VARCHAR(15), bill_pin_id SMALLINT UNSIGNED, bill_addr VARCHAR(175), name VARCHAR(60), tel VARCHAR(15))
BEGIN
DECLARE em_id INT UNSIGNED;
DECLARE addr_id INT UNSIGNED;
DECLARE cust_id INT UNSIGNED;
DECLARE sql_error TINYINT DEFAULT FALSE;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET sql_error = true;
--check if customer email already exists
SELECT id INTO em_id FROM email_list WHERE email = email;
-- not sure of the return value, haven't tested it
IF em_id = 1 THEN
-- if emails exists check if customer exists
SELECT id INTO cust_id FROM customer WHERE email_list_id = em_id;
IF cust_id = 1 THEN
-- customer must have address information
SELECT address_id INTO addr_id from customer_addr
INNER JOIN address on address_id = address.id
WHERE customer_id = cust_id and address = bill_addr;
END IF
END IF
START TRANSACTION;
IF (em_id = 0) THEN
-- if email doesn't exist, no customer no address - simple insert
INSERT INTO email_list (email, status) VALUES (email, status);
SELECT LAST_INSERT_ID() INTO em_id;
INSERT INTO customer (email_list_id, full_name, phone)
VALUES (em_id, name, tel);
SELECT LAST_INSERT_ID() INTO cust_id;
INSERT INTO address (pincode_id, address) VALUES (bill_pin_id, bill_addr);
SELECT LAST_INSERT_ID() INTO addr_id;
INSERT INTO customer_addr (address_id, customer_id)
VALUES (addr_id, cust_id);
ELSE
UPDATE email_list SET status = status where id = em_id;
UPDATE customer SET full_name = name, phone = tel WHERE id = cust_id;
UPDATE address SET pincode_id = bill_pin_id, address = bill_addr;
END IF
IF sql_error = FALSE THEN
COMMIT;
SELECT 'SUCCESS';
ELSE
ROLLBACK;
SELECT 'FAILED';
END IF
END //
DELIMITER ;
DELIMITERdirective, but it should have a way to select the statement delimiter, possibly a small drop-down or text box. The error suggests that your delimiter is not set.