SQLyog Function: Syntax error when returning values from INNER JOIN

Question

I am writing an SQLyog function to find the status of a client depending on the amount they have spent. The amount they have spent resides in the payment table and all the clients details reside in the clients table.

Edit: Note the function works when the output is just CustStatus, seems like the issue is to do with the inclusion of the other return values.

This is the error I am receiving:

Error Code: 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 ' VARCHAR(50), VARCHAR(50), VARCHAR(10) DETERMINISTIC

BEGIN DECLARE k1 INT(11' at line 2

DROP FUNCTION IF EXISTS DTC_VIP_members $$
CREATE FUNCTION DTC_VIP_members (v1 INT(11))
RETURNS INT(11), VARCHAR(50), VARCHAR(50), VARCHAR(10) DETERMINISTIC 
BEGIN 
DECLARE k1 INT(11);
DECLARE CustStatus VARCHAR(10);
SELECT SUM(payment.SaleAmt) INTO k1
FROM payment
INNER JOIN clients
ON payment.client_id = clients.client_id
WHERE payment.client_id = v1;
IF k1 >= 300 THEN
    UPDATE clients  
    SET clients.CustStatus = 'VIP';
ELSE
    UPDATE clients  
    SET clients.CustStatus = 'Non-VIP';
END IF;
RETURN clients.client_id, clients.first_name, clients.last_name, clients.CustStatus;
END $$

DELIMITER ;

Answer 1

SOLVED! Thank you for taking the time to look and reply Slava! Yes, your approach is a much better design and would probably all my silly SQL syntax errors. I learnt that with the SQLyog server version I am working on that Functions can only RETURN one value. So my design was flawed from the beginning.

Since the SQLyog function can only return single value “vip” or “non-vip”. I tried using a select such as to achieve the desired results. Select firstName, lastName, …. DTC_VIP_members(….);

Hopefully, this answer helps any other beginners trying to learn SQL from the free version of SQLyog. Updoots to the left

   DELIMITER $$

    DROP FUNCTION IF EXISTS `DTC_VIP_members`$$ 

    CREATE DEFINER=`yadda`@`%` FUNCTION `DTC_VIP_members`(v1 INT(11)) RETURNS VARCHAR(10) CHARSET latin1 
        DETERMINISTIC 
    BEGIN 
        DECLARE k1 INT(11); 
        DECLARE CustStatus VARCHAR(10); 
        SELECT SUM(SaleAmt) INTO k1 
        FROM payment 
        WHERE client_id = v1; 
        IF k1 >= 300 THEN
            SET CustStatus = 'VIP'; 
        ELSE
            SET CustStatus = 'Non-VIP'; 
        END IF;
        RETURN CustStatus; 
        END$$

    DELIMITER ;

    --------------------------------------

    SELECT  first_name, last_name, email, mobile_number, `DTC_VIP_members`(client_id) 
    FROM clients;