# ECHO

The `ECHO` command runs a stored procedure and returns a set of rows as a result.

## Syntax

**Positional Notation** is the standard way for passing arguments to functions.  All the arguments are specified in order. Arguments may be omitted from right to left as long as they have specified defaults.

```sql
ECHO { procedure_name([arg[, ...]])
       | database_name.procedure_name([arg [, ...]]) };
```

**Named Notation** is when the argument name is specified using => to separate it from the argument expression.

```sql
ECHO { procedure_name([arg=>val [,...]])
       | database_name.procedure_name([arg=>val [,...]])
```

**Mixed Notation** is a combination of positional and named notation. Note: named arguments cannot come before positional arguments.

```sql
ECHO { procedure_name([arg [, ...] [, arg=>val [,...] ]])
       | database_name.procedure_name([arg [, ...] [, arg=>val [,...] ]])

```

## Arguments

* `procedure_name`: The name of the stored procedure to run.
* `argument_list`: A list of optional arguments to pass as input parameters to the stored procedure.

## Remarks

* Stored procedures use a **late binding** approach when returning sets of rows. That is, a query object with parameter bindings is returned by a stored procedure, not a materialized row set. To get the set of rows for the query returned by a stored procedure, you must run the procedure using `ECHO`.
* You can consume the results of an `ECHO` statement from a client application program just like you consume the results of a `SELECT` statement.
* The [CALL](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/call.md) command is similar to `ECHO` but does not produce a set of rows as a result. Any query value returned is ignored when using `CALL`.
* 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.
* When using **Named Notation** and **Mixed Notation** the following are applicable:

  * Once a named argument is used, all arguments to the right must be named as well.
  * Named arguments cannot refer to other arguments.
  * Argument names are case-sensitive.
  * A different order of the same arguments may yield different plans.
* Overloaded stored procedures are supported. Refer to [Overloaded Functions and Stored Procedures](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/overloaded-functions-and-stored-procedures.md) for more information.
* Using **Named Notation** with overloaded stored procedures is not supported.

## Example - Run a Stored Procedure

You cannot use `ECHO` to run a stored procedure that does not return a value, or a stored procedure that returns an `ARRAY` or `RECORD` value. If the results of a stored procedure is a scalar value, the result of executing that stored procedure with `ECHO` will be a single row with the column name `RESULT`.

```sql
DELIMITER //
CREATE PROCEDURE return_scalar() RETURNS INT AS
BEGIN
    RETURN 2;
END //
DELIMITER ;

ECHO return_scalar();

```

```output

+--------+
| RESULT |
+--------+
|      2 |
+--------+
1 row in set

```

The behavior is different when the stored procedure returns a query type variable. Consider the following table and stored procedure:

```sql
CREATE TABLE t(a INT, b VARCHAR(30));
INSERT t VALUES(1, "red"), (2, "green"), (3, "blue");

DELIMITER //
CREATE PROCEDURE p(x INT) RETURNS query(a INT, b VARCHAR(30)) AS
DECLARE
  q query(a INT, b VARCHAR(30)) =
    SELECT a, b
    FROM t
    WHERE a >= x;
BEGIN
  RETURN q;
END //
DELIMITER ;

```

You can use the `ECHO` command to call this procedure, evaluate the query it returns, and output the resulting rows, as follows:

```sql
ECHO p(2);

```

```output

+------+-------+
| a    | b     |
+------+-------+
|    3 | blue  |
|    2 | green |
+------+-------+

```

Named notation and mixed notation can be used for the arguments of `ECHO`, similar to how named notation is used in `CALL`. Below shows the use of named notation with the stored procedure, `p`, created above. See [CALL](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/call.md) for more details on using named and mixed notation.

```sql
ECHO p(x=>2);

```

```output

+------+-------+
| a    | b     |
+------+-------+
|    3 | blue  |
|    2 | green |
+------+-------+

```

You can use `ECHO` command to obtain a scalar value from a stored procedure.

```sql
DELIMITER //
CREATE or REPLACE PROCEDURE p() RETURNS INT AS
BEGIN
    RETURN 17;
END //

DELIMITER ;
CALL p();
ECHO p();

```

```output

+---------+
| RESULT  |
+---------+
|    17   |
+---------+

```

**Consume Output of Echo in a Python Application**

A stored procedure may optionally return a `QUERY` value which evaluates to a set of rows. You can call a stored procedure from an application program and consume the set of rows produced by the query using the `ECHO` statement to run the procedure.

**Setup**

As an example, first run the following SQL statements:

```sql
-- Setup for Python ECHO example.
USE information_schema;
DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE test;

CREATE TABLE IF NOT EXISTS tbl(id INT, value VARCHAR(30));

INSERT INTO tbl VALUES(1, "red");
INSERT INTO tbl VALUES(2, "green");
INSERT INTO tbl VALUES(3, "blue");

DELIMITER //
CREATE OR REPLACE PROCEDURE p(x INT) RETURNS QUERY(id INT, value VARCHAR(30)) AS
DECLARE
    r QUERY(id INT, value VARCHAR(30)) = 
          SELECT id, value FROM tbl 
          WHERE id >= x;
BEGIN
    RETURN r;
END 
//
DELIMITER ;

```

**Python Application**

Then, make sure the [SingleStore Python library](https://github.com/memsql/memsql-python/) is installed.

Now run the following:

```python
import singlestoredb as s2

# Specify connection information for SingleStore Helios
HOST = "127.0.0.1"
PORT = 3306
USER = "root"
PASSWORD = ""

# Specify which database to work with.
DATABASE = "test"

def get_connection(db=DATABASE):
    """ Returns a new connection to the database. """
    return s2.connect(
        host=HOST, port=PORT, user=USER, password=PASSWORD, database=db)

def test_echo(x):
    with get_connection(db=DATABASE) as conn:
        query = 'echo p(%s)'
        with conn.cursor() as cur:
            cur.execute(query, [x])
            for r in cur:
                print("id = %s, value = %s" % (r[0], r[1]))
```

Now, `test_echo` can be called from the Python command line to display rows retrieved using `ECHO`. For example:

```
test_echo(2);

```

The following output is produced:

```
>>> test_echo(2);
id = 2, value = green
id = 3, value = blue

```

## Example - Run an Overloaded Stored Procedure

The following example creates and runs an overloaded stored procedure named `return_type` that accepts an `INT` or `BIGINT` as an input parameter.

```sql
DELIMITER //

CREATE PROCEDURE return_type(input INT) RETURNS TEXT AS
  BEGIN 
    RETURN "Integer"; 
  END //

CREATE PROCEDURE return_type(input BIGINT) RETURNS TEXT AS
  BEGIN 
    RETURN "Big Integer"; 
  END //

DELIMITER ;

```

Run the two variants.

```sql
ECHO return_type(1:>INT);

```

```output

+---------+
| RESULT  |
+---------+
| Integer |
+---------+

```

```sql
ECHO output_type(1:>BIGINT);

```

```output

+-------------+
| RESULT      |
+-------------+
| Big Integer |
+-------------+

```

***

Modified at: June 11, 2026

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

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