Mysql function not returning the expected result

Question

As I have mentioned in my question title below Mysql function returns null always :

CREATE DEFINER=`root`@`localhost` FUNCTION `nextCode`(tbl_name VARCHAR(30), prv_code VARCHAR(30)) RETURNS varchar(30) CHARSET utf8
    READS SQL DATA
BEGIN

    DECLARE nxtCode VARCHAR(30);

    SELECT ds.prefix, ds.suffix, ds.is_used, ds.next_number, CHAR_LENGTH(ds.pattern) 
    INTO @prefix, @suffix, @isUsed, @nxtNum, @pLength
    FROM ths_inventory.doc_sequnce ds WHERE ds.`table_name` = tbl_name;

    SET nxtCode = CONCAT(@prefix, LPAD((CASE WHEN @isUsed 
            THEN 
                (ExtractNumber(prv_code) + 1)
            ELSE 
                (@nxtNum) 
            END
            ), @pLength,'0'), @suffix);

    RETURN nxtCode;

END

But once I change the below line :

CONCAT(@prefix, LPAD((CASE WHEN @isUsed 
            THEN 
                (ExtractNumber(prv_code) + 1)
            ELSE 
                (@nxtNum) 
            END
            ), @pLength,'0'), @suffix)

To some static values like below :

CONCAT('PR', LPAD((CASE WHEN true 
                THEN 
                    (ExtractNumber(prv_code) + 1)
                ELSE 
                    (5) 
                END
                ), 6,'0'), '')

function start returning values accordingly.

Here is how I call my function :

nextCode('item','PR000002');

UPDATE:

I defined this function to get the next possible code for Item table :

According to my requirement the next possible code should be PR000000005. But instead of getting it, I always get empty result .

SELECT nextCode('item',(SELECT `code` FROM item ORDER BY id DESC LIMIT 1)) AS next_code;

Any help would be appreciable.

You can refer link: https://stackoverflow.com/questions/2754423/use-a-variable-for-table-name-in-mysql-sproc#9282179 And Prepared SQL statements syntax: https://dev.mysql.com/doc/refman/8.0/en/sql-syntax-prepared-statements.html — Prasad Wargad, Jul 09 '18 at 06:35
I don't think extractnumber is a mysql function are you sure you are using mysql? — P.Salmon, Jul 09 '18 at 06:44
@P.Salmon, I am using this custom function. https://stackoverflow.com/a/37269038/5454585 — Madushan Perera, Jul 09 '18 at 06:52
Did you check the content of `doc_sequnce`? Maybe you do not have an entry for `item` there, or any of the relevant values there is `null`. — Solarflare, Jul 09 '18 at 07:00
@Solarflare , Yes I checked.. But I have entry for item there .. :( — Madushan Perera, Jul 09 '18 at 07:06
@MadushanPerera: Query not working reason is limitations of User Defined Functions prohibit the use of dynamic sql, so this approach has no chance of success. Check below link: https://forums.mysql.com/read.php?118,90372,90577#msg-90577 which shows how to pass table name as parameter and creating a SQL query to get it executed. You can referred that term as "PREPARED SQL STATEMENTS" for which reference links were already shared. — Prasad Wargad, Jul 09 '18 at 07:10
@PrasadWargad He does not use dynamic SQL, he has a table that contains a tablename, this is valid — Solarflare, Jul 09 '18 at 08:36
@MadushanPerera Did you check for `null`-values too? (I am not sure if you got the relevance of it)? I could imagine that e.g. `ds.suffix` contains `null` instead of `''`, which would explain the behaviour you are experiencing. — Solarflare, Jul 09 '18 at 08:45
@Solarflare Yes ds.suffix was null so I replace it with `IFNULL(ds.suffix,'')` — Madushan Perera, Jul 09 '18 at 09:03

score 1 · Accepted Answer · answered Jul 09 '18 at 08:48

Run a query that uses the function, and then...

SELECT @prefix, @suffix, @isUsed, @nxtNum, @pLength;

...to inspect the values. The @ prefix means these are user-defined variables, so they have session scope, not program scope, and will still hold their values after the funcfion executes.

This should help pinpoint your problem.

But, you have two other problems you will need to solve after that.

SELECT ... INTO does not set the target variables when no row matches the query, so once you fix your issue, you will get very wrong results if you pass in arguments that don't match anything.

To resolve this, the function needs to set all these variables to null before the SELECT ... INTO query.

SET @prefix = NULL, @suffix = NULL, @isUsed = NULL, @nxtNum = NULL, @pLength = NULL;

See https://dba.stackexchange.com/a/35207/11651.

Also, your function does not handle concurrency, so two threads trying to find the "next" value for the same table, concurrently, will produce the same answer, so you will need to insure that your code handles this correctly with unique constraints and transactions or other appropriate locks.

Really appreciate your advises and suggestions. – Madushan Perera Jul 09 '18 at 09:08 — Madushan Perera, Jul 09 '18 at 09:08

Mysql function not returning the expected result

1 Answers1