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

Question

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. If possible can someone rewrite this in a way I can actually execute it?

Taurus Silver · Answer 1 · 2022-09-07T03:49:19.257

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);

P.Salmon · Answer 2 · 2022-09-07T14:11:48.757

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

There is a lot that needs change from sql-server please study closely

drop procedure if exists p;

delimiter $$
create procedure p(number int)
begin
 DECLARE vTYPE VARCHAR(1);

 SET vTYPE = (SELECT phones.type FROM phones WHERE phones.phone_number = Number) ;
     
 IF @TYPE = '1' then
        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%' ; 
 ELSE 
        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 IF ;

END $$

delimiter ;

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

2 Answers2