How the NAME_CONST() function works in Mariadb?

Syntax

The syntax of the NAME_CONST() function is as follows:

NAME_CONST(name, value)

The function takes two arguments:

name: A string that represents the name of the constant. This argument is mandatory.
value: Any expression that represents the value of the constant. This argument is mandatory.

The function returns the value of the constant, and also assigns the name to the constant in the result set.

Examples

Example 1: Using NAME_CONST() to create a dummy column

In this example, we use the NAME_CONST() function to create a dummy column in a result set. We use the SELECT statement to display the result.

SELECT NAME_CONST('Dummy', 42) AS Dummy, name, age FROM users;

The output is:

+-------+-------+-----+
| Dummy | name  | age |
+-------+-------+-----+
|    42 | Alice |  25 |
|    42 | Bob   |  30 |
|    42 | Carol |  28 |
+-------+-------+-----+

The NAME_CONST() function creates a column named Dummy with the value 42 for each row in the result set. The name and the value of the constant are visible in the output.

Example 2: Using NAME_CONST() to pass a parameter to a stored procedure

In this example, we use the NAME_CONST() function to pass a parameter to a stored procedure. We use the CALL statement to invoke the stored procedure.

-- Create a stored procedure that takes a parameter and prints its name and value
DELIMITER //
CREATE PROCEDURE show_param(IN param INT)
BEGIN
  SELECT param AS name, @param AS value;
END //
DELIMITER ;

-- Call the stored procedure with a parameter using NAME_CONST()
CALL show_param(NAME_CONST('MyParam', 100));

The output is:

+---------+-------+
| name    | value |
+---------+-------+
| MyParam |   100 |
+---------+-------+

The NAME_CONST() function passes the value 100 to the stored procedure, and also assigns the name MyParam to the parameter. The name and the value of the parameter are visible in the output.

Example 3: Using NAME_CONST() to debug a query

In this example, we use the NAME_CONST() function to debug a query. We use the SELECT statement to display the result.

-- Define a constant value using NAME_CONST()
SET @my_const = NAME_CONST('MyConst', 10);

-- Use the constant value in a query
SELECT @my_const * price AS total, name FROM products;

The output is:

+-------+--------+
| total | name   |
+-------+--------+
|   100 | Book   |
|   150 | Pen    |
|   200 | Laptop |
+-------+--------+

The NAME_CONST() function defines a constant value with the name MyConst and the value 10. The constant value is used in a query to calculate the total price of each product. The name and the value of the constant are visible in the output.

There are some other functions that are related to the NAME_CONST() function, such as:

LAST_INSERT_ID(): This function returns the value of the AUTO_INCREMENT column for the last inserted row. For example:

-- Create a table with an AUTO_INCREMENT column
CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  customer VARCHAR(50),
  amount DECIMAL(10,2)
);

-- Insert some rows into the table
INSERT INTO orders (customer, amount) VALUES
('Alice', 100.00),
('Bob', 150.00),
('Carol', 200.00);

-- Use LAST_INSERT_ID() to get the last inserted id
SELECT LAST_INSERT_ID() AS last_id;

The output is:

+---------+
| last_id |
+---------+
|       3 |
+---------+

The LAST_INSERT_ID() function returns the value of the id column for the last inserted row, which is 3.

CONCAT(): This function concatenates two or more strings into one string. For example:
```
-- Concatenate two strings
SELECT CONCAT('Hello', 'World') AS result;
```
The output is:
```
+------------+
| result     |
+------------+
| HelloWorld |
+------------+
```
The CONCAT() function concatenates the strings Hello and World into one string, which is HelloWorld.
CAST(): This function converts a value from one data type to another data type. For example:
```
-- Cast a string to an integer
SELECT CAST('42' AS INT) AS result;
```
The output is:
```
+--------+
| result |
+--------+
|     42 |
+--------+
```
The CAST() function converts the string 42 to an integer, which is 42.

Conclusion

The NAME_CONST() function is a utility function that assigns a name to a constant value. This function is mainly used for debugging purposes, as it allows us to see the name and the value of a constant in the output of a query. This function is also useful for creating dummy columns in a result set, or for passing parameters to a stored procedure. The function returns the value of the constant, and also assigns the name to the constant in the result set. There are some other functions that are related to the NAME_CONST() function, such as LAST_INSERT_ID(), CONCAT(), and CAST(). These functions can help us to work with different data types and values in Mariadb.

Syntax

Examples

Example 1: Using NAME_CONST() to create a dummy column

Example 2: Using NAME_CONST() to pass a parameter to a stored procedure

Example 3: Using NAME_CONST() to debug a query

Related Functions

Conclusion