# CREATE PROCEDURE

The `CREATE PROCEDURE` command 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](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/query.md) values. A query-type value is typically evaluated by calling the procedure with [ECHO](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/echo.md) to produce a rowset.

> **📝 Note**: Stored Procedures check for compilation errors upon creation; however, runtime errors are not checked. So it is possible to create a syntactically correct procedure that refers to a non-existent SQL object which will result in an error when run.

## Syntax

```sql
CREATE [OR REPLACE] PROCEDURE procedure_name ( [parameter_list] )
  [RETURNS { data_type [data_type_modifier] } ] 
  [AUTHORIZE AS DEFINER] | AUTHORIZE AS CURRENT_USER] [DEFINER = ‘user’[@’host’] 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.

If [function overloading](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/overloaded-functions-and-stored-procedures.md) is not enabled, 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 built-in functions such as `CONCAT()`, `ABS()`, `COUNT()`, and so on.

If function overloading is enabled, overloaded UDFs or overloaded stored procedures (SPs) that share a name can be created. All other procedure names must be unique within a database and cannot duplicate names for other stored procedures, tables, views, UDFs, TVFs, UDAFs, or built-in functions.

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](https://docs.singlestore.com/db/v9.1/reference/sql-reference/database-object-case-sensitivity.md) 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](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-types.md) 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](https://docs.singlestore.com/db/v9.1/reference/sql-reference/character-encoding.md).

**AUTHORIZE AS DEFINER ] | AUTHORIZE AS CURRENT\_USER \[DEFINER = ‘user’\[@’host’]** is an optional parameter. It determines the security permissions of potential executors of a stored procedure. A stored procedure that uses the `AUTHORIZE AS DEFINER [DEFINER =‘user’[@’host’]]` option is executed using the security permissions of the definer assigned to that stored procedure. The definer must have `EXECUTE` permission. A stored procedure created using the `AUTHORIZE AS CURRENT_USER` option, the stored procedure is executed using the current user's security permissions. Users must have `EXECUTE` permission to execute stored procedures created with the `AUTHORIZE AS CURRENT_USER` option.

If `AUTHORIZE AS DEFINER ] | AUTHORIZE AS CURRENT_USER [DEFINER = ‘user’[@’host’]` options are not used when creating a stored procedure, the definer is the user who created it by default. All other users must have `EXECUTE` permission to execute the stored procedure.

Creating a stored procedure with different user permissions than the stored procedure creator can be useful for securing your database and ensuring that the appropriate actions are performed only by authorized users. See the [Procedural Extensions Security](https://docs.singlestore.com/db/v9.1/developer-resources/procedural-extensions/procedural-extensions-security.md) page for more details on how the `DEFINER` and `CURRENT_USER` security model works.

Here is a stored procedure example where the executor permissions will be the same as the definer's permissions. It is assumed the definer has `EXECUTE` permission:

```sql
DELIMITER // 
CREATE PROCEDURE proc1() AUTHORIZE AS DEFINER DEFINER = 'test1'@'%'AS
BEGIN 
ECHO SELECT CURRENT_USER(); 
END;//

```

```sql
CALL proc1();

```

```output

+---------------+
| CURRENT_USER()|
+---------------+
| test1@%       |
+---------------+
```

Here is a stored procedure example where the permissions are the user executing the object, not the definer. Users must have `EXECUTE` permission to run the stored procedure:

```sql
DELIMITER // 
CREATE PROCEDURE proc2() AUTHORIZE AS CURRENT_USER DEFINER = 'test1'@'%' AS
BEGIN 
ECHO SELECT CURRENT_USER(); 
END;//
```

```sql
CALL proc2();

```

```output

+----------------+
| CURRENT_USER() |
+----------------+
| root@%        |
+----------------+
```

Here is a stored procedure example where the definer is the user creating the object by default. The permissions are the user executing the object, not the definer. Users must have the `EXECUTE` permission:

```sql
DELIMITER // 
CREATE PROCEDURE proc3() AUTHORIZE AS CURRENT_USER AS
BEGIN 
ECHO SELECT CURRENT_USER(); 
END;//

```

```sql
CREATE USER test1;
```

```sql
GRANT SELECT, EXECUTE ON db.* TO 'test1'@'%';
```

If you log back into the cluster as the test1 user and execute the `proc3` stored procedure:

```sql
CALL proc3();

```

```output

+----------------+
| CURRENT_USER() |
+----------------+
| test1@%        |
+----------------+
```

**data\_type**

Any scalar-valued, non-scalar-valued, or query data type. Scalar and non-scalar-valued types are described in [Data Types](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-types.md). 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](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml.md) 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](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/echo-select.md) for more details and examples.

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

* Any [CALL](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/call.md) 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.
* The `%ROWTYPE` attribute gives a record type that mirrors a table row and the `%TYPE` attribute designates the type of a referenced scalar object, such as another variable or column. Using these attributes can make it easier to program stored procedures and anonymous code blocks by reducing the amount of code that needs to be written. Refer to the [%ROWTYPE and %TYPE](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/rowtype-and-type.md) page for more information.
* Refer to the [Permissions Matrix](https://docs.singlestore.com/db/v9.1/reference/sql-reference/security-management-commands/permissions-matrix.md) for the required permissions.

## 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](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/mysql-client-delimiters.md) topic for details on MySQL client delimiters.

## Security and Permissions

Stored procedures in SingleStore can use the [Definer Security Model or the Current User Security Model](https://docs.singlestore.com/db/v9.1/developer-resources/procedural-extensions/procedural-extensions-security.md). 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](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/call.md) from within other stored procedures
* Using [CALL](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/call.md) or [ECHO](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/echo.md) 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).

## 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](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/create-function-udf.md) 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;

```

```output

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

```

Arguments to stored procedures can use positional, named, or mixed notation. Refer to [CALL](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/call.md) for more details.

## 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](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/call.md) 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](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/profile.md) 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](https://docs.singlestore.com/db/v9.1/query-data/query-tuning.md) 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.

> **📝 Note**: There is an easier-to-use method to obtain the same expanded data using a [recursive common table expression](https://docs.singlestore.com/db/v9.1/query-data/advanced-query-topics/with-common-table-expressions.md) which requires only a single SQL statement.

```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");

```

```output

+------+--------+---------+-------+
| 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");

```

```output

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

```

```sql
CALL get_emp_tree("Jill");

```

```output

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

```

```output

+----------------------------+----------+-------+
| 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();

```

```output

+--------------------------------+
| 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();

```

```output

+------+------------+
| 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();

```

```output

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

```

```output

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");

```

```output

+----------------+-------------------------+
| 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");

```

```output

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

```

```output

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

```

```output

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

```

Note that arrays of other supported [data types](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-types.md) 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](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/control-flow-statements.md), [transaction management](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/transactions-in-stored-procedures.md), and [exception handling](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/exceptions-and-exception-handling.md).

This command causes implicit commits. Refer to [COMMIT](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/commit.md) for more information.

## Related Topics

* [CREATE TEMPORARY PROCEDURE](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/create-or-replace-temporary-procedure.md)
* [DROP PROCEDURE](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/drop-procedure.md)
* [SHOW FUNCTIONS](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/show-functions.md)
* [SHOW CREATE FUNCTION](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/show-create-function.md)
* [Control Flow Statements](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/control-flow-statements.md)
* [Transactions in Stored Procedures](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/transactions-in-stored-procedures.md)
* [Exceptions and Exception Handling](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/exceptions-and-exception-handling.md)

***

Modified at: June 11, 2026

Source: [/db/v9.1/reference/sql-reference/procedural-sql-reference/create-procedure/](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/create-procedure/)

(An index of the documentation is available at /llms.txt)