What is the correct way to declare and use MarisDB user defined variables?

All we need is an easy explanation of the problem, so here it is.

I’m using MariaDB version 10.3.28-MariaDB-cll-lve

This site https://mariadb.com/kb/en/user-defined-variables/ says

User-defined variables names must be preceded by a single at character (@)

That’s what I usually do but recently I was rushing, forgot the ‘@ and wrote the following function which seems to work fine without the variables having ‘@’ at the front.

So is having a ‘@’ in front of a user defined variable a firm requirement or not?

If it is, why does my function work correctly without it?

DELIMITER $$
CREATE  FUNCTION get_next_redacted_member_id() RETURNS INT(11)
BEGIN
       DECLARE result INT SIGNED;
       DECLARE lastid INT;
       INSERT INTO redacted_member_id ( date_used) VALUES ( NOW() ); 
       SET lastid = (SELECT LAST_INSERT_ID() );
       SET result =  -1 * lastid ;
       RETURN result;
END$$
DELIMITER ;

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

The main difference ist that you can use DECALRE ony in stored function and procedzres

DECLARE var_name

This statement is used to declare local variables within stored programs.

see manual

User-Defined Variables

User-defined variables are variables which can be created by the user and exist in the session.

see manual

Which means every user defined variable is accessible every where(in a session) not only in a stored procedure/function. So you set your user defined Variable in a stored procudure, you can get the value eve after the stored procedure ends. That is very helpful for debugging such procedures/functions.

Also when you DECLARE a variable, you set also its data type and size.

With user defined MysQL decides what it needs when You SET @a = 123455

Method 2

Replace

   SET lastid = (SELECT LAST_INSERT_ID() );
   SET result =  -1 * lastid ;
   RETURN result;

with

   SELECT -LAST_INSERT_ID();

and toss the two declarations.

If you have a more complex case, bring it on.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply