MySQL - trying to execute a sql statement based on a condition-CodePudding

DECLARE @TYPE AS VARCHAR(1)

    SET @TYPE = (SELECT phones.type FROM phones WHERE phones.phone_number = [Number])
     
IF @TYPE = '1'

    BEGIN
        SELECT phones.phone_number as PhoneMobile, phones.phone_number as EntityID, clients.id as contactid, first_name as firstname, last_name as lastname 
        FROM clients INNER JOIN phones ON clients.id = phones.type_id 
        WHERE (((phones.is_deleted)=0) AND ((phones.type)=1) 
        AND ((phones.phone_number) LIKE '%[Number]%')) END; 

ELSE 

    BEGIN 
        SELECT phones.phone_number as PhoneMobile, phones.phone_number as EntityID, prospects.id as contactid, first_name as firstname, last_name as lastname 
        FROM prospects INNER JOIN phones ON prospects.id = phones.type_id 
        WHERE (((phones.is_deleted)=0) AND ((phones.type)=2) 
        AND ((phones.phone_number) LIKE '%[Number]%'))END;
END IF

END

Above is the SQL Statement. Below is the error I keep receiving:

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 @TYPE AS VARCHAR(1)  SET @TYPE = (SELECT phones.type FROM phones WHERE p' at line 1

Can somebody let me know what i'm doing wrong? I'm not the best at MySQL. Looking for some insight.

CodePudding user response：

DECLARE IN Stored Procedure must place after BEGIN

DECLARE before BEGIN that mean createing ERROR HANDLER (AFTER FIRST BEGIN)

BEGIN
 -- exit if the duplicate key occurs
    DECLARE CONTINUE HANDLER FOR 1062
    BEGIN
    SELECT CONCAT('Duplicate key (',inSupplierId,',',inProductId,') occurred') AS message;
    END;
    
    -- insert a new row into the SupplierProducts
    INSERT INTO SupplierProducts(supplierId,productId)
    VALUES(inSupplierId,inProductId);

CodePudding user response：

It looks like you have found some sql-server code and attempted to run it in mysql. In mysql you cannot have code blocks outwith stored programs (procedure,function,trigger,event) , same applies to if..else..end if code constructs, you cannot declare user defined variables see How to declare a variable in MySQL? and you can only declare local variables in stored programs. Be careful when creating stored programs to set delimiters see - https://dev.mysql.com/doc/refman/8.0/en/stored-programs-defining.html