What is the correct version of this sql function?

Question

What is the correct way to generate a new id as varchar? Here I'm trying to convert from SQL Server to MySQL database.

Help needed.

I tried internet but it did not solve my problem.

--*GENERATES ANY ID WHEN NEED*
DELIMITER //

CREATE FUNCTION getNewID(needTable VARCHAR(20)) RETURNS VARCHAR(10)
BEGIN
    DECLARE lastvalue VARCHAR(10);
    DECLARE i INT;
    DECLARE newId VARCHAR(10);

    IF needTable = 'Item'
            SELECT lastvalue = MAX(resourceID) FROM Item;

            SELECT MAX(resourceID) INTO lastvalue FROM Item;

            IF IS NULL(lastvalue)
                SET lastvalue = 'I00000000';
                SET i = RIGHT(lastvalue,9) + 1;
                SET newId = 'S' + RIGHT('00000000'+CONVERT(VARCHAR(10),i),9);

            RETURN newId;
END; //

DELIMITER ;

SELECT getNewID ('Item');

DROP FUNCTION getNewID

The error says:

Error code 1064, SQL state 42000
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT MAX(resourceID) INTO lastvalue FROM Item;

IF IS NULL(lastval' at line 10
Line 3, column 1

Execution finished after 0 s, 1 error(s) occurred.

Answer 1

Try this

DELIMITER //

CREATE FUNCTION getNewID(needTable VARCHAR(20)) RETURNS VARCHAR(10)
BEGIN
    DECLARE lastvalue, newId VARCHAR(10);
    DECLARE i INT;

    SELECT MAX(resourceID) INTO lastvalue FROM Item where needTable = 'Item';

            IF(lastvalue IS NULL) THEN
                SET lastvalue = 'I00000000';
                SET i = RIGHT(lastvalue,9) + 1;
                SET newId = 'S' + RIGHT('00000000'+CONVERT(VARCHAR(10),i),9); -- what you are trying to do here?
                END IF;
-- what you need to return whenlastvalue is not null?
            RETURN newId;
END //

DELIMITER ;