DaviesTobi alex DaviesTobi alex - 1 year ago 75
SQL Question

Am trying to create a stored procedure in mysql phpmyadmin

Am trying to get a field and if it doesn't exist, I wish to insert it into the table but am having an error. For the first query I tried

CREATE PROCEDURE reg_val( IN pid VARCHAR(30), IN pyd VARCHAR(50), IN dat VARCHAR(20), OUT value VARCHAR(50) )
BEGIN TRANSACTION
DECLARE @id AS VARCHAR(50)
SELECT @id = paymentid FROM table WHERE phoneid=pid
IF @id IS NULL
BEGIN
INSERT INTO user_info (phoneid, paymentid, created_at) VALUES (pid, pyd, dat)
SELECT @id = SCOPE_IDENTITY()
END
SELECT @id
COMMIT TRANSACTION


I get this error


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 @id AS VARCHAR(50) SELECT @id = paymentid FROM table WHERE phoneid=' at line 3


so I couldn't fix this and I tried this new query

CREATE PROCEDURE reg_val( IN pid VARCHAR(30), IN pyd VARCHAR(50), IN dat VARCHAR(20), OUT value VARCHAR(50) )
BEGIN
IF EXISTS (SELECT phoneid FROM user_info WHERE phoneide=pid) THEN
SELECT paymentid FROM user_info WHERE phoneid=pid
ELSE
INSERT INTO user_info (phoneid, paymentid, created_at) VALUES (pid, pyd, dat)
SELECT paymentid FROM user_info WHERE phoneid=SCOPE_IDENTITY()
END IF


but I also get this error


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 'ELSE THEN INSERT INTO user_info (phoneid, paymentid, created_at) VAL' at line 5


Maybe my understanding of forming complex queries is off but any help would be appreciated.

Ali Ali
Answer Source

Please change you Stored Procedure as below (check the changes from comments):

DELIMITER $$   // change delimiter
CREATE PROCEDURE reg_val( IN pid VARCHAR(30), IN pyd VARCHAR(50), IN dat VARCHAR(20), OUT VALUE VARCHAR(50)  )
    BEGIN
        IF EXISTS (SELECT phoneid FROM user_info WHERE phoneide=pid) THEN
           SELECT paymentid FROM user_info WHERE phoneid=pid; // use semicolon
        ELSE 
           INSERT INTO user_info (phoneid, paymentid, created_at) VALUES (pid, pyd, dat);// use semicolon
           SELECT paymentid FROM user_info WHERE phoneid=SCOPE_IDENTITY();// use semicolon
        END IF;// use semicolon
        END;// use end and semicolon