Defining variable in Mariadb

user_16898197 0
Viewed 447

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

  1. Unrecognized data type. (near ")" at position 54)
  2. 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.

declare mariadb
1 Answers

A few syntax matters:

  1. Need a () set after the function name, even if no parameters are used:

    CREATE FUNCTION test()

  2. 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)

  3. 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

  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.

user_76440180
Related