# COLLECT

The `COLLECT` command runs a `SELECT` statement having the [QUERY](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/query.md) type and stores the result as an array of records.

After `COLLECT` runs you will typically iterate through the array to perform processing on its elements.

## Syntax

```
variable_name = COLLECT(query_type_value)

variable_name = COLLECT(select_statement, QUERY(field_definition [, ...]))

  field_definition:
    field_name data_type [data_type_modifier]

```

## Arguments

**query\_type\_value**

A [query type](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/query.md) value.

**field\_definition**

The number of fields in the `field_definition` must be the same as the number of columns that the `select_statement` will return. The `data_type`(s) in the `field_definition` do not have to be the same as the data types of the columns that the `select_statement` will return. If the data types do not match, the `:>` operator will automatically be used to convert the column type to the field type. The `field_name`(s) in the `field_definition` also need not be the same as the field names in the `select_statement`.

**field\_name**

The name of the field.

**data\_type**

Any scalar-valued data type. For a complete list of data types, see the [Data Types](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-types.md) topic.

**data\_type\_modifier**

If not present or `NULL`, indicates that field\_name may contain `NULL` values in the resulting array.

If `NOT NULL`, indicates that field\_name may not contain `NULL` values in the resulting array.

**select\_statement**

A string containing a SQL `SELECT` statement.

## Remarks

Some database systems implement cursors, which allow programmers to iterate through each row of a recordset and perform an action on the row. SingleStore does not use the term “cursor.” However, you can achieve read-only cursor functionality by calling `COLLECT` and iterating over the values in the resulting array. The array may be processed forwards, backwards or in an arbitrary order.

Expressions of the form `record.field` for a record type value cannot be substituted directly into a SQL statement in a stored procedure. To include a record field’s value in an SQL statement, assign the value into a variable, and use the variable in the SQL statement instead. This approach is used in example 1 below, with the variables `_id` and `_name`.

## Example 1: Using `COLLECT` with Static Queries

In the following example, `COLLECT` uses a query type variable, whose definition `SELECT * from t` is static.

```sql
DROP DATABASE IF EXISTS singlestore_docs_example;
CREATE DATABASE singlestore_docs_example;
USE singlestore_docs_example;
CREATE TABLE t(id INT, name TEXT);
CREATE TABLE output_log(msg TEXT);

INSERT INTO t VALUES (1, 'red'), (2, 'green'), (3, 'blue');

DELIMITER //

CREATE OR REPLACE PROCEDURE p() AS
DECLARE  
  qry QUERY(id INT, name TEXT) = SELECT id, name FROM t;
  arr ARRAY(RECORD(id INT, name TEXT));
  _id INT;
  _name TEXT;
BEGIN
  arr = COLLECT(qry);
  FOR x in arr LOOP
    _id = x.id;
    _name = x.name;
    INSERT INTO output_log VALUES(CONCAT('[', _id, ', ', _name, ']'));
  END LOOP;
END //

DELIMITER ;

CALL p();

SELECT * FROM output_log ORDER BY msg;

```

```output

+------------+
| msg        |
+------------+
| [1, red]   |
| [2, green] |
| [3, blue]  |
+------------+

```

> **📝 Note**: Instead of using variable assignments and verbose definitions, you can shorten the procedure definition (and body) by inserting the `COLLECT` function directly inside the loop as:```sql
> DELIMITER //
>
> CREATE OR REPLACE PROCEDURE p() AS
> DECLARE
>   qry QUERY(id INT, name TEXT) = SELECT id, name FROM t;
> BEGIN
>   FOR x IN COLLECT(qry) LOOP
>     INSERT INTO output_log VALUES(CONCAT('[', x.id, ', ', x.name, ']'));
>   END LOOP;
> END //
>
> DELIMITER ;
>
> ```The next example demonstrates the use of this style of code.

## Example 2: Using `COLLECT` with Dynamic Queries (Method 1)

Here, you call `COLLECT` using a query type variable, whose value is populated by the `TO_QUERY` function. `TO_QUERY` allows you to write [dynamic queries](https://docs.singlestore.com/db/v9.1/developer-resources/procedural-extensions/static-parameterized-sql.md).

> **⚠️ Warning**: The `TO_QUERY` function does not parameterize dynamic SQL queries, which results in unique plans being added to the in-memory and on-disk plancache. If you use this function extensively, care should be taken to manage the size of your plancache.See [Managing Plancache Memory and Disk Usage](https://docs.singlestore.com/db/v9.1/user-and-cluster-administration/maintain-your-cluster/managing-memory/managing-plancache-memory-and-disk-usage.md) for more details on how to drop a plan from the in-memory plancache and how to delete the plancache files on disk.If you need to define a query-type variable from a static query that use stored procedure parameters (e.g. `SELECT t.a, t.b FROM t WHERE t.c = x` with `x` being the input parameter value), you can do that directly instead of using `TO_QUERY`. This will parameterize any literal values and re-use the same plan from the plancache the next time. For an example of how to do this, see [Binding Variables to a Query Type Value](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/query.md).

```sql
DROP DATABASE IF EXISTS singlestore_docs_example;
CREATE DATABASE singlestore_docs_example;
USE singlestore_docs_example;
CREATE TABLE t1(id INT, name TEXT);
CREATE TABLE t2(id INT, name TEXT);
CREATE TABLE output_log(msg TEXT);

INSERT INTO t1 VALUES (1, 'red'), (2, 'green'), (3, 'blue');
INSERT INTO t2 VALUES (1, 'orange'), (2, 'blue'), (3, 'purple');

DELIMITER //

CREATE OR REPLACE PROCEDURE p (tbl TEXT) AS
DECLARE  
  qry QUERY(id INT, name TEXT) = TO_QUERY(CONCAT('SELECT id, name FROM ' , tbl));
BEGIN
  FOR x IN COLLECT(qry) LOOP
    INSERT INTO output_log VALUES(CONCAT('[', x.id, ', ', x.name, ']'));
  END LOOP;
END //

DELIMITER ;

CALL p('t2');

SELECT * FROM output_log ORDER BY msg;

```

```output

+-------------+
| msg         |
+-------------+
| [1, orange] |
| [2, blue]   |
| [3, purple] |
+-------------+

```

## Example 3: Using `COLLECT` with Dynamic Queries (Method 2)

Here, you specify a [dynamic query](https://docs.singlestore.com/db/v9.1/developer-resources/procedural-extensions/static-parameterized-sql.md) directly in a `COLLECT` statement. The first argument is the query, in text form. The second argument is a `QUERY` type which specifies the output schema of the query, so it is known at the time the `COLLECT` expression is compiled.

```sql
DROP DATABASE IF EXISTS singlestore_docs_example;
CREATE DATABASE singlestore_docs_example;
use singlestore_docs_example;
CREATE TABLE t1(id INT, name TEXT);
CREATE TABLE t2(id INT, name TEXT);
CREATE TABLE output_log(msg TEXT);

INSERT INTO t1 VALUES (1, 'red'), (2, 'green'), (3, 'blue');
INSERT INTO t2 VALUES (1, 'orange'), (2, 'blue'), (3, 'purple');

DELIMITER //
CREATE or REPLACE PROCEDURE p(tbl TEXT) AS
DECLARE  
BEGIN
  FOR x in COLLECT(CONCAT('SELECT id, name FROM ' , tbl), QUERY(id INT, name TEXT)) LOOP
    INSERT INTO output_log VALUES(CONCAT('[', x.id, ', ', x.name, ']'));
  END LOOP;
END //
DELIMITER ;

CALL p('t2');

SELECT * FROM output_log ORDER BY msg;

```

```output

+-------------+
| msg         |
+-------------+
| [1, orange] |
| [2, blue]   |
| [3, purple] |
+-------------+

```

**Related Topics**

* [CREATE PROCEDURE](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/create-procedure.md): The `CREATE PROCEDURE` command creates a stored procedure.
* [QUERY](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/query.md): A data type representing a `SELECT` statement.
* [TO\_QUERY](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/to-query.md): Converts a SQL string to a query type value.

***

Modified at: June 12, 2026

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

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