1

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 ;
2
  • the use of user defined variables with @ will some day be obsolze, but i still can't understand how we can get rid of them completely, as we need them for prepared statements. it is a good way to see lokal defined variables if you markl them diffrently from colum names
    – nbk
    Apr 5 '21 at 11:28
  • Yes, I can see its a good idea. But it looks like it's no longer enforced Apr 5 '21 at 12:17
0

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

5
  • The 'main difference' between what? My questions was why the documentation says we must proceed the variable name with '@' but my function seems to indicate that is not true. Are you saying that variables without the @ symbol act differently to those with it? Apr 5 '21 at 13:53
  • no, DECLARE set the datatype and SET @a mysql check which daRARYPE FILTS: the scope od DECLARE is the function or procidure and @a has the complete session
    – nbk
    Apr 5 '21 at 14:36
  • So are you saying that the scope of DECLARE myvar INT (without a'@' symbol) is just the function but the scope of DECLARE @myvar INT (with a @ symbol) is the entire session? Apr 5 '21 at 16:13
  • you can't declare a @myvar
    – nbk
    Apr 5 '21 at 16:27
  • ah. ok, i understand. answer accepted Apr 5 '21 at 18:37
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.