DaviesTobi alex DaviesTobi alex - 6 months ago 19
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

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