Defining variable in Mariadb

I tried a lot of ways to use user defined variables in MariaDB version 10.3.22. After failing to use it in my application, I wanted to try with a simple example:

DECLARE @EmpName1 NVARCHAR(50)
SET @EmpName1 = 'Ali'
PRINT @EmpName1

gives Unrecognized statement type. (near "DECLARE" at position 0)

After some digging around I tried using it between delimiters and as a created function:

DELIMITER //
CREATE FUNCTION test
DECLARE @EmpName1 VARCHAR(50)
SET @EmpName1 = 'Ali'
PRINT @EmpName1 
END //
DELIMITER;

This gives

Unrecognized data type. (near ")" at position 54)
A "RETURNS" keyword was expected. (near "END" at position 110)

I cannot figure out where the issue might be coming from, as the MariaDB documentation has the same syntax as far as I can see.

Can anyone help solving this issue? My final goal would be to assign the single result of a query to a variable as a string.

Solution

A few syntax matters:

Need a () set after the function name, even if no parameters are used:
```
CREATE FUNCTION test()
```
A function's return data type must be specified after that: (I used the same type/size as your variable. Can be some other type, of course, depending upon what is being returned)
```
CREATE FUNCTION test() returns varchar(50)
```
The use of @ with the variables not needed, also missing ; at the end of each line, plus PRINT is invalid:
```
DECLARE EmpName1 VARCHAR(50);
SET EmpName1 = 'Ali';
-- PRINT EmpName1; see item 4
```

Functions are expected to return a value:

RETURN EmpName1; -- I simply replaced the PRINT with RETURN here.

Putting that all together, the complete definition becomes:

DELIMITER //

CREATE FUNCTION test() RETURNS VARCHAR(50)
BEGIN
    DECLARE EmpName1 VARCHAR(50) DEFAULT '';
    SET EmpName1 = 'Ali';
    RETURN EmpName1;
END //

DELIMITER ;

Then after that is created, use the function:

SELECT test();

Example interaction:

root@localhost(test) DELIMITER //
    ->     CREATE FUNCTION test() RETURNS VARCHAR(50)
    ->     BEGIN
    ->         DECLARE EmpName1 VARCHAR(50);
    ->         SET EmpName1 = 'Ali';
    ->         RETURN EmpName1;
    ->     END //
Query OK, 0 rows affected (0.07 sec)

root@localhost(test)
root@localhost(test)  DELIMITER ;

root@localhost(test) select test();
+--------+
| test() |
+--------+
| Ali    |
+--------+
1 row in set (0.09 sec)

Though the website does not use DELIMITER you can also see this in action at this DB fiddle.