In this MariaDB tutorial, we will learn, how to create a procedure in MariaDB. Here we saw a few examples of the “MariaDB create procedure” and with the following topics.
- MariaDB create procedure
- MariaDB create procedure syntax error
- How to create procedure with parameters in MariaDB
- MariaDB create procedure not parameters
- How to create procedure command line in MariaDB
- MariaDB create procedure table
- MariaDB show create procedure
- MariaDB grant create procedure
Create procedure in MariaDB
A stored program is a procedure in MariaDB that helps us in passing the parameter into it.
The full syntax to create the procedure is given below.
CREATE
[ DEFINER = { current_user | user_name } ]
PROCEDURE procedure_name
[ (parameter_name data type, parameter_name data type) ]
[ LANGUAGE SQL
| DETERMINISTIC
| NOT DETERMINISTIC
| { CONTAINS SQL
| NO SQL
| READS SQL DATA
| MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'comment_value'
BEGIN
declare_section
execution_section
END;
Where parameters are,
DEFINER clause
This one is Optional. The definer clause is used to define the user who creates the function. So to use the different definer, we need to write the DEFINER keyword with user_name, and this user_name is the new definer of the function.
Procedure_name
The name that we want to give to a new Procedure in MariaDB.
Parameter_name
More than one parameter can be provided to the function. While creating a new procedure, all parameter needs to be the input parameters.
Return_datatype
We mention what kind of value the procedure will return, after doing some calculation or processing, if the procedure returns the integer, then the data type of the procedure will be INT type.
LANGUAGE SQL
When LANGUAGE SQL is specified, then it determines that the procedure returns only a single result provided a list of parameters to the procedure.
NOT DETERMINISTIC
It is just the opposite of the clause LANGUAGE SQL, It may give different results provided the list of parameters to the procedure.
CONTAINS SQL
CONTAINS SQL clause is default clause and an instructive clause that instructs MariaDB that the procedure is going to have SQL.
READS SQL DATA
It is an instructive clause that instructs the MariaDB procedure that reads data with help of SELECT statements but it will not change the data.
MODIFIES SQL DATA
It is an instructive clause that instructs the MariaDB procedure to change SQL data using data definition language statements like INSERT, DELETE, UPDATE.
Declare_section
In the declare section, local variables are declared.
Execution_section
In the execution_section, where SQL query or code is written.
Let’s create the procedure as calci using the below code
DELIMITER //
CREATE procedure Calci ( OUT ending_value INT )
DETERMINISTIC
BEGIN
DECLARE total_value INT;
SET total_value = 10;
label1: WHILE total_value <= 300 DO
SET total_value = total_value * 4;
END WHILE label1;
SET ending_value = total_value;
END; //
DELIMITER ;
Call this procedure.
CALL Calci(@hello);
SELECT @hello;
In the above code, we are storing the result from the Calci into @hello. Then select or view the information from @hello in the SELECT statement.

The output shows the result as 640.
This is how to create a procedure in MariaDB.
Also, check: How to Change Column in MariaDB
MariaDB create procedure syntax error
Suppose we are a user in a database company or we are the database developer of any company which exists in the United States. While executing some of the procedures based queries on the database, we get the error related to syntax.
To resolve the error problems we should always visit the official documentation of MariaDB. This kind of documentation contains all the rules to write the query correctly.
MariaDB create procedure with parameters
The procedure in the MariaDB can contain the parameters like IN for input and OUT for output, as we have learned in the above first sub-section of this tutorial. Parameters are used to exchange or pass the data between stored procedures and functions.
Let’s create the procedure as ‘simplecount‘ that will count the number of records in the table large_csv.
DELIMITER //
CREATE PROCEDURE simplecount (OUT param1 INT)
BEGIN
SELECT COUNT(*) INTO param1 FROM large_csv;
END;
//
DELIMITER ;
CALL simplecount(@a);
SELECT @a;

From the above output, we can see that large_csv table contains 28,338 records.
This is how to create a procedure with parameters in MariaDB.
Also, read: MariaDB Update Statement
MariaDB create procedure not parameters
In the above sub-section of this section, we have created the procedure with parameters, so here we will create the procedure with no parameters in the MariaDB.
Here we are going to create the procedure as no_paramater that will show all data of the table large_csv.
DELIMITER //
CREATE PROCEDURE no_parameter()
BEGIN
SELECT * FROM large_csv;
END;
//
DELIMITER ;
CALL no_parameter();

This is how to create a procedure without parameters in MariaDB.
Read: How to Remove User in MariaDB
MariaDB create procedure command line
In the command line, we can create the MariaDB procedure, the command line is like a Linux terminal which is also called command-line interface ( CLI ), so here we are going to create the procedure in the command line.
Let’s create the same procedure Calci using the command line.
DELIMITER //
CREATE procedure Calci ( OUT ending_value INT )
DETERMINISTIC
BEGIN
DECLARE total_value INT;
SET total_value = 10;
label1: WHILE total_value <= 300 DO
SET total_value = total_value * 4;
END WHILE label1;
SET ending_value = total_value;
END; //
DELIMITER ;

As we can see that we have created a procedure in the command line.
Read: How to Grant All Privileges in MariaDB
MariaDB create procedure table
The procedure can create the new table or we can create the procedure that will create a new table in the MariaDB using the CREATE PROCEDURE statement.
Let’s create the procedure for creating the new table.
DELIMITER //
CREATE procedure new_tbl ()
DETERMINISTIC
BEGIN
CREATE TABLE tbl(id INT, NAME VARCHAR(10));
END; //
DELIMITER ;
The above code in the block BEGIN and END will create the new table as tbl with two columns id and NAME.
Call this procedure.
CALL new_tbl;
SHOW COLUMNS FROM tbl;

The output shows the created table from procedure new_tbl.
This is how to create a table from a procedure in MariaDB.
Read: How to Create View in MariaDB
MariaDB show create procedure
Here, we will create the procedure that will show the table in the current MariaDB, the same command will be used here and that is the CREATE PROCEDURE.
Let’s create the procedure as show_tbl to know the number of tables in the current database.
DELIMITER //
CREATE procedure show_tbl ()
DETERMINISTIC
BEGIN
SHOW TABLES;
END; //
DELIMITER ;
Call the procedure using the below query.
CALL show_tbl;

So here in the output, we showed all tables in the current database using the procedure.
Read: Replace Function in MariaDB
MariaDB grant create procedure
In MariaDB, we can grant the privileges on various MariaDB objects, we can grant users the capability to EXECUTE these procedures in MariaDB.
The syntax is given below.
GRANT EXECUTE ON [ PROCEDURE | FUNCTION ] object TO user;
- EXECUTE: which means the capability to execute a procedure.
- PROCEDURE: This is used when the privilege to be granted on a procedure.
- object: It is the database name to whom privileges is granted. If we are granting the privileges to a procedure then it should the name of that procedure.
- user: The name of the user who is going to have the EXECUTE privileges.
Let’s grant the EXECUTE privilege to user root on procedure Calci using the below code.
GRANT EXECUTE ON PROCEDURE Calci TO 'root'@'localhost';
This is how to grant permission to create a procedure in MariaDB.
You may also like to read the following tutorials on MariaDB.
- MariaDB Vs SQLite
- MariaDB Timestamp
- MariaDB DateTime Tutorial
- MariaDB ERROR 1064
- How to import CSV files in MariaDB
- MariaDB Reset Root Password
- How to Create Trigger in MariaDB
Here we learned how to create a procedure in MariaDB and a few examples of MariaDB create procedure.
- MariaDB create procedure
- MariaDB create procedure syntax error
- MariaDB create procedure with parameters
- MariaDB create procedure not parameters
- MariaDB create procedure command line
- MariaDB create procedure table
- MariaDB show create procedure
- MariaDB grant create procedure
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.