Creates a stored procedure. A stored procedure is a callable routine that accepts input parameters, executes programmatic logic, and optionally returns a single value. Stored procedures can return all the value types that UDFs can return, but stored procedures can also return query-type values. A query-type value is typically evaluated by calling the procedure with ECHO to produce a rowset.

Syntax

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:

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:

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:

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:

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:

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

data_type

Any scalar-valued, non-scalar-valued, or query data type. Scalar and non-scalar-valued types are described in the Data Types topic. 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

The majority of Data Manipulation Language statements are supported in the body of a stored procedure. However, only the following Data Definition Language statement types are supported in the body of a stored procedure:

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

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.

MySQL Client Delimiters

When creating a procedure using a MySQL-compatible client connected to SingleStore DB, 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 DB 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 DB

• 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

When using client applications like the mysql command line interface, Sequel Pro, or other similar tools to connect to SingleStore DB, 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.

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:

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:

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:

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:

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:

CALL charge_account(1, 200.0000);

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

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:

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

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.

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.

CALL ticket_sales_by_minute();
SHOW PROFILE;

See the Query Tuning Guide 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.

-------- 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:

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 |
+------+--------+---------+-------+

CALL get_emp_tree("Felix");
****
+------+--------+-------+-------+
| id   | mgr_id | name  | level |
+------+--------+-------+-------+
|    7 |      3 | Jill  |     1 |
|    6 |      3 | Andy  |     1 |
|    3 |      1 | Felix |     0 |
+------+--------+-------+-------+

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.

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 ;

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

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

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 |
+----------------------------+----------+-------+

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.

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.

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 |
+----------------+------------+

Additional Capabilities

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

Related Topics

• DROP PROCEDURE
• SHOW FUNCTIONS
• SHOW CREATE FUNCTION
• Control Flow Statements
• Transactions in Stored Procedures
• Exceptions and Exception Handling

Was this Article Helpful?

Thanks For Your Feedback!