How to make mysql function that return from the select result?

Question

I want to create this function on mySql 8. It will create a sequence number like 00001,00002

CREATE FUNCTION dbOne.create_sequence_number(lastNumber CHAR(255), numberLength INT, lastValue CHAR(255) ) RETURNS char(255)
BEGIN
    DECLARE select_var CHAR(255);
    SET select_var = (SELECT 
        CASE WHEN lastNumber = lastValue 
        THEN
        LPAD( '1', numberLength, '0' ) 
        ELSE 
        LPAD(CAST(( CAST(COALESCE ( lastNumber, '0' ) AS INT) + 1 ) AS VARCHAR, numberLength, '0' ) INTO select_var);
    RETURN select_var;
END

i dunno whats wrong with this query but i always got this error.

SQL Error [1064] [42000]: 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 'INT) + 1 ) AS VARCHAR, numberLength, '0' ) INTO select_var);
    RETURN select_var' at line 9
  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 'INT) + 1 ) AS VARCHAR, numberLength, '0' ) INTO select_var);
    RETURN select_var' at line 9
  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 'INT) + 1 ) AS VARCHAR, numberLength, '0' ) INTO select_var);
    RETURN select_var' at line 9

I also tried with this query.

CREATE FUNCTION erhav2_db.create_sequence_number(lastNumber CHAR(255), numberLength INT, lastValue CHAR(255) ) RETURNS char(255)
BEGIN
    DECLARE select_var CHAR(255);
    SELECT 
        (CASE WHEN lastNumber = lastValue 
        THEN
        lpad( '1', numberLength, '0' ) 
        ELSE 
        lpad(CAST(( CAST(COALESCE ( lastNumber, '0' ) AS INT) + 1 ) AS VARCHAR, numberLength, '0' ))) INTO select_var;
    RETURN select_var;
END

but still gave me with the same error. What could go wrong with my function query ?

also look here -- this is an easier way to pad a number with 0 https://stackoverflow.com/a/16760967/215752 — Hogan, May 24 '22 at 03:41
The `CASE` statement is not having an `END`. Also `CAST` to INT and VARCHAR will give you error. Try UNSIGNED instead on INT and CHAR instead of VARCHAR. [cast-from-varchar-to-int-mysql](https://stackoverflow.com/questions/12126991/cast-from-varchar-to-int-mysql) — Abinash, May 24 '22 at 03:50

score 1 · Accepted Answer · answered May 24 '22 at 05:30

CREATE FUNCTION dbOne.create_sequence_number(
    lastNumber /* CHAR(255) */ UNSIGNED, 
    numberLength INT, 
    lastValue CHAR(255) 
) 
RETURNS CHAR(255)
RETURN LPAD(CASE WHEN lastNumber = lastValue
                 THEN 1
                 ELSE COALESCE(lastNumber, 0) + 1
                 END,
            numberLength, 
            '0');

Multiple datatype convertions are excess - MySQL will change the datatype implicitly according to the operation context.

All operations can be performed in single statement which makes both declared variable and BEGIN-END (and delimiter reassigning) unnecessary.

The code needs lastNumber to be convertable to numeric datatype. If not then both your and my code will fail in strict SQL mode. So I recommend to change the datatype of lastNumber CHAR(255) input parameter datatype to UNSIGNED / INT - this will allow to detect the value incorrectness on the function call stage, not in the function code.

i got this error `SQL Error [1418] [HY000]: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)` — Gagantous, May 24 '22 at 06:09
@Gagantous What a problem? Add correct definitions (obviously, DETERMINISTIC and NO SQL). — Akina, May 24 '22 at 07:21

How to make mysql function that return from the select result?

1 Answers1