CREATE PROCEDURE

Syntax

SQL

CREATE [OR REPLACE] PROCEDURE procedure_name ( [parameter_list] )
  [RETURNS { data_type [data_type_modifier] } ] 
   AS
  [DECLARE variable_list] [ ...n ]
  BEGIN
    procedure_body
  END ;

parameter_list:
  parameter_name data_type [data_type_modifier [ ...n ] ] [, ...]


data_type_modifier:
  DEFAULT default_value | NOT NULL | NULL | COLLATE collation_name

variable_list:
  variable_name type_expression [= value_expression ] ; [... ;]

Arguments

OR REPLACE

If specified, replaces a stored procedure if one already exists with the same name.

procedure_name

The name of the stored procedure. Procedure names must be unique within a database, and cannot be duplicates of names for other stored procedures, tables, views, user-defined scalar-valued functions (UDFs), user-defined table-valued functions (TVFs), user-defined aggregate functions (UDAFs), or other builtin functions such as CONCAT(), ABS(), COUNT(), and so on.

You can also specify database_name and procedure_name together by replacing procedure_name with database_name.procedure_name instead of defaulting to the current context database. For example, you can write the following:

SQL

CREATE PROCEDURE db.some_func(a int)
...

Procedure names are not case-sensitive. For details on case-sensitivity, refer to the Database Object Case-Sensitivity topic.

parameter_list

Input parameters are optional. Any number of input parameters can be specified, and each must be separated by a comma (,). Each input parameter must be given a unique identifier name within the scope of the stored procedure.

The following example shows how to declare a single input parameter:

SQL

CREATE PROCEDURE single_param_example(a INT)
...

Stored procedures allow scalar data types, non-scalar data types (ARRAY and RECORD), and query data types as input parameters. Each valid type is described in the Data Types topic. The following example demonstrates how to declare more than one input parameter, using scalar, non-scalar, and query data types:

SQL

CREATE PROCEDURE multi_param_example(
  a INT, b ARRAY(BIGINT NOT NULL), c DECIMAL(19,4), d QUERY(col1 TEXT))
...

Default values can be specified for input parameters by using the DEFAULT constraint. You cannot set a default value for a query type variable. Consider the following example:

SQL

CREATE PROCEDURE default_example(a INT DEFAULT 5)
...

Input parameters can also specify data type modifiers, namely NOT NULL, NULL, and COLLATE. Query type variables cannot be null or have a collation. Consider the following example:

SQL

CREATE PROCEDURE modifier_example(a TEXT NOT NULL COLLATE utf8_bin)
...

You can use parameters with any supported character sets using the following syntax.

SQL

CREATE PROCEDURE procedure_name ( [parameter_name data_type COLLATE collation_name], [...n] ) 
...

In a similar fashion, you can use variables with any supported character sets. In addition, you can specify the collation for tables and columns introduced in procedural extensions.

For more information, see Character Encoding.

data_type

Any scalar-valued, non-scalar-valued, or query data type. Scalar and non-scalar-valued types are described in Data Types. A query-type value is a query, which may contain bound parameters, that can evaluated to produce a row set, or stored in a query-type variable.

procedure_body

A vast majority of Data Manipulation Language statements including EXPLAIN <query> (but not EXPLAIN BALANCE), PROFILE <query>, and all SHOW commands are supported in the body of a stored procedure. Additionally, only the following Data Definition Language statement types are supported in the body of a stored procedure:

ALTER TABLE
ANALYZE TABLE
COMMIT
CREATE INDEX
CREATE GROUP
CREATE PIPELINE
CREATE ROLE
CREATE TABLE
CREATE USER
CREATE VIEW
DROP ... FROM PLANCACHE
DROP GROUP
DROP INDEX
DROP PLAN
DROP ROLE
DROP TABLE
DROP USER
DROP VIEW
ECHO SELECT
FILL CONNECTION POOLS
FLUSH CONNECTION POOLS
GRANT
GRANT GROUP
GRANT ROLE
GRANT TO ROLE
KILLALL QUERIES
OPTIMIZE PIPELINE
OPTIMIZE TABLE
REVOKE
REVOKE FROM ROLE
REVOKE ROLE FROM GROUP
ROLLBACK
SET
SHOW PROFILE
SNAPSHOT DATABASE
START PIPELINE
START TRANSACTION
STOP PIPELINE
TRANSACTION
TRUNCATE TABLE

Note

SingleStore does not allow DDL commands in stored procedures that are called from pipelines.

When running stored procedures with DML queries on reference tables, you must connect to the master aggregator. This is because forwarding is NOT supported for DML queries in stored procedures.

Note

SELECT statements may be used if assigned to a QUERY type variable or if used as part of an ECHO SELECT statement. In ECHO SELECT, the SELECT statement will execute and the resulting rows will be returned to the client as part of a multi-result set. This is useful for debugging or returning multiple results from a single stored procedure call. See ECHO SELECT for more details and examples.

In addition to the SQL statements listed above, the following statements are also supported:

Any CALL statement to execute another stored procedure.
Execution of a UDF if it is used as an expression inside a query, or alternately if it is on the right side of an assignment.
Execution of a TVF if it is inside the FROM clause of a DML statement.
Variable assignments.
Refer to the Permission Matrix for the required permission.

MySQL Client Delimiters

When creating a procedure using a MySQL-compatible client connected to SingleStore, you must change the client delimiter to ensure that the procedure definition is correctly passed to the server and then set it back to a semicolon after the alternate delimiter is no longer needed. See the MySQL Client Delimiters topic for details on MySQL client delimiters.

Security and Permissions

Stored procedures in SingleStore use the Definer Security Model. The SP’s definer must have EXECUTE permissions on every UDF or SP that is used in the procedure body, and the permissions to execute every SQL statement that appears in the procedure body.

Variable and Parameter Substitution in SQL Statements

You may include the value of a parameter or a variable (a system-defined global or session variable) in a SQL query inside a stored procedure by using its name in a place where a constant value may appear. You can even set a global or session variable inside a stored procedure. User-defined variables cannot be referenced inside a stored procedure; use a local variable instead.

Limitations

Stored procedures are limited in the following ways:

Calling Limitations

A stored procedure can only be invoked in one of the following ways:

Using CALL from within other stored procedures
Using CALL or ECHO from a client application connected to SingleStore.
Inside an expression from within other stored procedures, for example:
```
varName = storedProcedureName ( argument_list )
```

Stored procedures cannot be invoked within SQL queries, views, user-defined scalar-valued functions (UDFs), user-defined table-valued functions (TVFs), or user-defined aggregate functions (UDAFs).

Function Overloading

A stored procedure’s definition cannot be overloaded by changing the procedure signature, such as adding or removing input parameters or changing the return type. This limitation is directly correlated to the naming restrictions mentioned in the arguments section above.

MySQL Client Delimiters Limitations

When using client applications like the mysql command line interface, Sequel Pro, or other similar tools to connect to SingleStore, you must bound the CREATE PROCEDURE statement with delimiters other than semi-colon. This is so that the semi-colons in the procedure won’t be interpreted as the end of the statement by the client tool. Then you should set the delimiter to semi-colon again afterwards. This is the reason for the use of DELIMITER // and DELIMITER ; in the example below.

The DELIMITER commands must be on independent lines. These lines are interpreted by the client, not the server. See the CREATE FUNCTION topic for additional discussion of MySQL client delimiters.

When creating stored procedures or functions from your own client application created in a language such as Java, C++, or Python, the delimiters are not needed.

Examples

Formatting Data Using Stored Procedures

The following example uses a stored procedure to format data before insertion into the courses table.

SQL

CREATE TABLE courses(course_code TEXT, section_number INT, number_students INT);

DELIMITER //

CREATE OR REPLACE PROCEDURE courses_sp (course_code TEXT, section_number INT, number_students INT) AS
  DECLARE
    code TEXT = UCASE(course_code);
    num_students INT = number_students + 1;
  BEGIN
    INSERT INTO courses VALUES (code, section_number, num_students);
END //

DELIMITER ;

Call courses_sp four times, where the courses table initially contains no records:

SQL

CALL courses_sp("cs-101",1,13);
CALL courses_sp("cs-201",1,11);
CALL courses_sp("cs-201",2,8);
CALL courses_sp("cs-301",1,14);

SELECT * FROM courses ORDER BY course_code, section_number;

+-------------+----------------+-----------------+
| course_code | section_number | number_students |
+-------------+----------------+-----------------+
| CS-101      |              1 |              14 |
| CS-201      |              1 |              12 |
| CS-201      |              2 |               9 |
| CS-301      |              1 |              15 |
+-------------+----------------+-----------------+

Bank Transaction Example

The following example demonstrates how to use a stored procedure to perform a calculation on a bank account and, if sufficient funds are present, deduct the charge amount from the balance.

This example uses two tables:

SQL

CREATE TABLE accounts (
  id BIGINT AUTO_INCREMENT,
  first_name VARCHAR(255),
  last_name VARCHAR(255),
  PRIMARY KEY (id)
);

CREATE TABLE account_balance (
  account_id BIGINT,
  remaining_balance DECIMAL(18,4),
  PRIMARY KEY (account_id)
);

The following example user and balance is inserted into the account:

SQL

INSERT INTO accounts VALUES(DEFAULT, "John", "Doe");
INSERT INTO account_balance VALUES(1, 500.0000);

Now that the example tables have been created with test data, you can create the stored procedure. The procedure is defined in the following way:

SQL

DELIMITER //
CREATE PROCEDURE charge_account(id BIGINT, amount DECIMAL(18,4)) AS
  DECLARE
    balance_tbl QUERY(bal DECIMAL(18,4)) =
      SELECT remaining_balance
      FROM account_balance
      WHERE account_id = id;
    balance DECIMAL(18,4) = SCALAR(balance_tbl);
    updated_balance DECIMAL(18,4) = balance - amount;
  BEGIN
    IF balance > amount THEN
      UPDATE account_balance
      SET remaining_balance = updated_balance
      WHERE account_id = id;
    END IF;
  END //
DELIMITER ;

You can execute this procedure by using the CALL command in the client:

SQL

CALL charge_account(1, 200.0000);

Next, execute the following SELECT statement to see the account’s new balance.

SQL

SELECT account_balance.account_id, account_balance.remaining_balance,
accounts.first_name, accounts.last_name
FROM accounts, account_balance
WHERE accounts.id = account_balance.account_id;

This produces the following results:

SQL

+------------+-------------------+------------+-----------+
| account_id | remaining_balance | first_name | last_name |
+------------+-------------------+------------+-----------+
|          1 |          300.0000 | John       | Doe       |
+------------+-------------------+------------+-----------+

Query Tuning Example with PROFILE

The following is an example of how to include PROFILE in a stored procedure. This is useful for queries within stored procedures whose performance impacts may not be otherwise surfaced.

First, a table called ticket_sales is created and populated with example data.

SQL

CREATE TABLE if NOT EXISTS ticket_sales(
order_id int,
order_date datetime,
key(order_id),
shard(order_id));

INSERT INTO ticket_sales VALUES(1, now());

/* Run the below command until 524288 rows of data have been inserted into the table. */

INSERT INTO ticket_sales
SELECT order_id+(SELECT max(order_id) FROM expedited_orders), now()
FROM ticket_sales;

Then, a stored procedure for counting the number of ticket sales by minute, and profiling the query that does this is created.

SQL

DELIMITER //
CREATE PROCEDURE ticket_sales_by_minute() as
BEGIN
EXECUTE IMMEDIATE "ECHO SELECT COUNT(distinct order_id), MINUTE(order_date) FROM ticket_sales GROUP BY 2";
EXECUTE IMMEDIATE  "PROFILE SELECT COUNT(distinct order_id), MINUTE(order_date) from ticket_sales GROUP BY 2";
END //
DELIMITER ;

Once the procedure has been called, SHOW PROFILE can easily be run in order to see the profile metrics of the query in the stored procedure. SHOW PROFILE can also be included in the stored procedure if you would like to see profile metrics each time the procedure is called.

SQL

CALL ticket_sales_by_minute();
SHOW PROFILE;

See Query Tuning for further guidance on using profile statistics to improve query performance.

Recursive Tree Expansion Example

The following example shows how to expand a tree represented by data in a table. The tree is assembled in a temporary table and echoed back to the client as a way to return its contents. This approach will allow multiple users on different sessions to call the stored procedure concurrently.

SQL

-------- Recursive tree expansion example
--
-- Important points:
--   Only references from child to parent are used.
--   The number of queries run (trips through the loop)
--   is proportional to the height of the tree.

CREATE TABLE emp(id int, mgr_id int, name varchar(30));
INSERT emp VALUES(1, NULL, "Maggie");  -- boss
INSERT emp VALUES(2, 1, "John");
INSERT emp VALUES(3, 1, "Felix");
INSERT emp VALUES(4, 2, "Joan");
INSERT emp VALUES(5, 2, "Richard");
INSERT emp VALUES(6, 3, "Andy");
INSERT emp VALUES(7, 3, "Jill");

/*
Diagram of tree represented by the rows above:

             Maggie
          /         \
         John        Felix
         /  \          /   \
     Joan   Richard   Andy  Jill
*/

-- Find all employees under specific manager and output them into
-- table emps_at_and_below_mgr.

DELIMITER //
CREATE OR REPLACE PROCEDURE get_emp_tree(mname varchar(30))
AS
DECLARE
  current_level int = 0;
  num_inserted int;
  result_q query(id int, mgr_id int, name varchar(30), level int) =
    SELECT id, mgr_id, name, level FROM emps_at_and_below_mgr;
BEGIN
  CREATE TEMPORARY TABLE emps_at_and_below_mgr(
    id int, mgr_id int, name varchar(30), level int);
  DELETE FROM emps_at_and_below_mgr;
  INSERT INTO emps_at_and_below_mgr
   SELECT *, 0
   FROM emp WHERE name = mname;
  num_inserted = 1;
  WHILE num_inserted <> 0 LOOP
    INSERT INTO emps_at_and_below_mgr
      SELECT emp.*, current_level + 1
      FROM emp, emps_at_and_below_mgr t
      WHERE emp.mgr_id = t.id and t.level = current_level;
    num_inserted = row_count();
    current_level += 1;
  END LOOP;
  ECHO SELECT id, mgr_id, name, level from result_q;
  DROP TABLE emps_at_and_below_mgr;
END //
DELIMITER ;

CALL get_emp_tree("Maggie");
CALL get_emp_tree("Felix");
CALL get_emp_tree("Jill");

The results of the CALL statements above illustrate the tree expansion:

SQL

CALL get_emp_tree("Maggie");

+------+--------+---------+-------+
| id   | mgr_id | name    | level |
+------+--------+---------+-------+
|    1 |   NULL | Maggie  |     0 |
|    4 |      2 | Joan    |     2 |
|    5 |      2 | Richard |     2 |
|    2 |      1 | John    |     1 |
|    3 |      1 | Felix   |     1 |
|    7 |      3 | Jill    |     2 |
|    6 |      3 | Andy    |     2 |
+------+--------+---------+-------+

SQL

CALL get_emp_tree("Felix");

+------+--------+-------+-------+
| id   | mgr_id | name  | level |
+------+--------+-------+-------+
|    7 |      3 | Jill  |     1 |
|    6 |      3 | Andy  |     1 |
|    3 |      1 | Felix |     0 |
+------+--------+-------+-------+

SQL

CALL get_emp_tree("Jill");

+------+--------+------+-------+
| id   | mgr_id | name | level |
+------+--------+------+-------+
|    7 |      3 | Jill |     0 |
+------+--------+------+-------+

Calling Stored Procedures Inside Expressions

The following example shows how a stored procedure can be called inside an expression from within another stored procedure.

SQL

CREATE TABLE reading(dt DATETIME(6), sensorID INT, value FLOAT);

INSERT reading VALUES(NOW(6), 1, 5.0);
INSERT reading VALUES(NOW(6), 1, 5.5);
INSERT reading VALUES(NOW(6), 2, 4.0);

DELIMITER //

CREATE OR REPLACE PROCEDURE getLastReading(_sensorID INT) RETURNS FLOAT AS
DECLARE
  q query(value FLOAT) =
   SELECT value
   FROM reading
   WHERE sensorID = _sensorID
       AND dt = (SELECT max(dt) FROM reading WHERE sensorID = _sensorID);
BEGIN
  RETURN SCALAR(q);
END //

CREATE OR REPLACE PROCEDURE getLastReadings() AS
DECLARE
  r1 FLOAT;
  r2 FLOAT;
BEGIN
  r1 = getLastReading(1);
  r2 = getLastReading(2);
  ECHO SELECT(CONCAT('reading 1 = ', r1 , '; reading 2 = ', r2)) AS msg;
END //

DELIMITER ;

Note

The following statements (in the code block above) show how stored procedures that return a scalar value can also be called on the right hand side of an assignment operation (within another stored procedure).

r1 = getLastReading(1);
r2 = getLastReading(2);

You can also embed the call to a stored procedure nested down inside an expression. For example,

var = CEIL(sp(2) + (ABS((TO_NUMBER('123') + sp(1)))));

SQL

SELECT * FROM reading ORDER BY dt;

+----------------------------+----------+-------+
| dt                         | sensorID | value |
+----------------------------+----------+-------+
| 2019-09-10 09:54:50.453440 |        1 |     5 |
| 2019-09-10 09:54:50.468527 |        1 |   5.5 |
| 2019-09-10 09:54:50.473642 |        2 |     4 |
+----------------------------+----------+-------+

SQL

CALL getLastReadings();

+--------------------------------+
| msg                            |
+--------------------------------+
| reading 1 = 5.5; reading 2 = 4 |
+--------------------------------+

Nested `DECLARE` Blocks

The following example shows how you can use nested DECLARE blocks inside a stored procedure.

SQL

DELIMITER //
CREATE OR REPLACE PROCEDURE nestEx() AS
DECLARE
  x INT = 5;
BEGIN
  DECLARE y INT = x*x;
  BEGIN
    ECHO SELECT x AS 'X', y AS 'X(squared)';
  END;
END //
DELIMITER ;
CALL nestEx();

+------+------------+
| X    | X(squared) |
+------+------------+
|    5 |         25 |
+------+------------+

Using Variables inside Stored Procedures

The following example shows how to set and reference the values of engine variables inside a stored procedure.

SQL

DELIMITER //

CREATE OR REPLACE PROCEDURE setVar() AS
DECLARE ver DOUBLE;
BEGIN
  ver = @@memsql_version;
  ECHO SELECT ver AS Version, @@memsql_version_date AS Dated;
  SET GLOBAL cardinality_estimation_level = '7.1';
  SET autocommit = 0;
  ECHO SELECT @@cardinality_estimation_level AS CardinalityLvl, @@autocommit AS Autocommit;
END //

DELIMITER ;

CALL setVar();

+---------+--------------------------------+
| Version | Dated                          |
+---------+--------------------------------+
|     7.3 | Mon Oct 12 16:12:11 2020 +0000 |
+---------+--------------------------------+

+----------------+------------+
| CardinalityLvl | Autocommit |
+----------------+------------+
| 7.1            |          0 |
+----------------+------------+

Specifying Collation in Stored Procedures

The following example illustrates a stored procedure that has parameters and literals used with utf8mb4 in variables initialized with a DECLARE statement.

SQL

SET COLLATION_SERVER = utf8mb4_general_ci;

Query OK, 0 rows affected (0.00 sec)

SQL

CREATE TABLE feedback(product_rating VARCHAR(30), customer_service_rating VARCHAR(30));

INSERT feedback VALUES("Happy😊", "Neutral😐"), ("Happy😊", "Happy😊"), ("Neutral😐", "Neutral😐");

DELIMITER //
CREATE OR REPLACE PROCEDURE sample_procedure(customer_feedback VARCHAR(30))
  RETURNS QUERY(product_rating VARCHAR(30), customer_service_rating VARCHAR(30)) AS
/*Literals and parameters use the utf8mb4 character set in variables*/
DECLARE q QUERY(product_rating VARCHAR(30), customer_service_rating VARCHAR(30)) = SELECT product_rating, customer_service_rating FROM feedback WHERE product_rating = "Happy😊" AND customer_service_rating = customer_feedback;
BEGIN
  RETURN q;
END //
DELIMITER ;
 
ECHO sample_procedure("Neutral😐");

+----------------+-------------------------+
| product_rating | customer_service_rating |
+----------------+-------------------------+
| Happy😊        | Neutral😐               |
+----------------+-------------------------+

The following example demonstrates a stored procedure that has parameters and literals used with utf8mb4 in variables initialized with a SELECT … INTO statement.

SQL

CREATE TABLE feedback(product_rating VARCHAR(30) COLLATE utf8mb4_general_ci, customer_service_rating VARCHAR(30)) COLLATE utf8mb4_general_ci;
INSERT feedback VALUES("Happy😊", "Neutral😐"), ("Happy😊", "Happy😊"), ("Neutral😐", "Neutral😐");
DELIMITER //
CREATE OR REPLACE PROCEDURE procedure_example(customer_feedback VARCHAR(30) COLLATE utf8mb4_general_ci)
AS
/*Literals and parameters use the utf8mb4 character set in variables*/
  DECLARE feedback_count INT;
BEGIN
  SELECT COUNT(*) INTO feedback_count FROM feedback WHERE product_rating = "Happy😊" AND customer_service_rating = customer_feedback;
  ECHO SELECT feedback_count;
END //
DELIMITER ;
 
CALL procedure_example("Neutral😐");

+----------------+
| feedback_count |
+----------------+
|              1 |
+----------------+
 
Query OK, 0 rows affected (0.06 sec)

Note that all supported character sets can also be used in dynamic SQL.

Passing Arrays to Stored Procedures

The following example shows how to pass an integer type array as an argument to a stored procedure:

SQL

DELIMITER //

CREATE OR REPLACE PROCEDURE arExec(spec ARRAY(INT)) AS
DECLARE
  res VARCHAR(255) = '';
BEGIN
  FOR i IN 0 .. LENGTH(spec) - 1 LOOP
    res = CONCAT(res, " ", spec[i]);
  END LOOP;

  ECHO SELECT res;
END //

DELIMITER ;

CALL arExec([1,2,3] :> ARRAY(INT));

+--------+
| res    |
+--------+
|  1 2 3 |
+--------+

The following example demonstrates a stored procedure that has a RECORD type array as an argument:

SQL

DELIMITER //

CREATE OR REPLACE PROCEDURE arExec(spec ARRAY(RECORD(ID INT, Code TEXT))) AS
DECLARE
  res VARCHAR(255) = '';
BEGIN
  FOR i IN 0 .. LENGTH(spec) - 1 LOOP
    res = CONCAT(res, " ", spec[i].ID, " ", spec[i].Code);
  END LOOP;

  ECHO SELECT res;
END //

DELIMITER ;

CALL arExec([ROW(1,'XSD'), ROW(2, 'PRT')] :> ARRAY(RECORD(ID INT, Code TEXT)));

+--------------+
| res          |
+--------------+
|  1 XSD 2 PRT |
+--------------+

Note that arrays of other supported data types can also be passed as an argument to a stored procedure.

Additional Capabilities

The body of a stored procedure can contain statements for control flow, transaction management, and exception handling.

This command causes implicit commits. Refer to COMMIT for more information.

On this page

Syntax

Arguments

MySQL Client Delimiters

Security and Permissions

Variable and Parameter Substitution in SQL Statements

Limitations

Calling Limitations

Function Overloading

MySQL Client Delimiters Limitations

Examples

Formatting Data Using Stored Procedures

Bank Transaction Example

Query Tuning Example with PROFILE

Recursive Tree Expansion Example

Calling Stored Procedures Inside Expressions

Nested `DECLARE` Blocks

Using Variables inside Stored Procedures

Specifying Collation in Stored Procedures

Passing Arrays to Stored Procedures

Additional Capabilities

Was this article helpful?

On this page

Was this article helpful?

CREATE PROCEDURE

On this page

Syntax

Arguments

MySQL Client Delimiters

Security and Permissions

Variable and Parameter Substitution in SQL Statements

Limitations

Calling Limitations

Function Overloading

MySQL Client Delimiters Limitations

Examples

Formatting Data Using Stored Procedures

Bank Transaction Example

Query Tuning Example with PROFILE

Recursive Tree Expansion Example

Calling Stored Procedures Inside Expressions

Nested DECLARE Blocks

Using Variables inside Stored Procedures

Specifying Collation in Stored Procedures

Passing Arrays to Stored Procedures

Additional Capabilities

Related Topics

Was this article helpful?

On this page

Was this article helpful?

Nested `DECLARE` Blocks