# QUERY

SingleStore Helios Procedural SQL (PSQL) supports the `QUERY` data type, whose value represents a SQL `SELECT` statement, optionally with bound variable values.

Query type values may be returned from a stored procedure, assigned as a value into a variable, passed as a parameter to a stored procedure, evaluated to return a rowset to a client using [ECHO](https://docs.singlestore.com/cloud/reference/sql-reference/procedural-sql-reference/echo.md), or evaluated using [COLLECT](https://docs.singlestore.com/cloud/reference/sql-reference/procedural-sql-reference/collect.md) or [SCALAR](https://docs.singlestore.com/cloud/reference/sql-reference/procedural-sql-reference/scalar.md) functions.

[TO\_QUERY](https://docs.singlestore.com/cloud/reference/sql-reference/procedural-sql-reference/to-query.md) may be used to convert a dynamic `SELECT` statement to a query type.

**Query Type Definition Syntax**

The following syntax declares a query type value.

```
name QUERY(field_definition [, ...]) [ = select_statement]

  field_definition:
    field_name data_type [data_type_modifier]

```

## Arguments

**name**

The name of the query variable or parameter.

**field\_definition**

A query type declaration will contain one or more `field_definition`s.

The number of `field_definition`s must be the same as the number of columns that the `select_statement` will return.

The `data_type`s in the `field_definition`s 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.

Field names specified in the `select_statement` are irrelevant; The `field_name`s in the `field_definition` are instead used in the QUERY’s result set.

**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/cloud/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 QUERY’s result set.

If `NOT NULL`, indicates that field\_name may not contain `NULL` values in the QUERY’s result set.

**select\_statement**

A SQL `SELECT` statement, optionally referencing parameters and variables from the current stored procedure. If the `SELECT` statement is built dynamically using a string, the string should be passed to [TO\_QUERY](https://docs.singlestore.com/cloud/reference/sql-reference/procedural-sql-reference/to-query.md). `TO_QUERY` will convert the string to a query type value.

The `select_statement` is optional because it does not need to be specified when the QUERY is declared. The `select_statement` may be assigned to the query variable at a later time.

## Remarks

After a query variable has been declared, new fields cannot be added and existing fields cannot be changed.

When calling a procedure, input parameters and return types are always passed by value. This behavior applies to all types, including queries.

## Declaration and Initialization

Query types are declared in a procedure as either an input parameter or as a variable in the `DECLARE` block. For example, the function below uses both a query type variable and a query type parameter, and returns one or the other, at random:

```sql

DELIMITER //
CREATE OR REPLACE PROCEDURE p(x QUERY(a INT, b VARCHAR(30))) -- x is a query type parameter
RETURNS QUERY(a INT, b VARCHAR(30)) AS
DECLARE
  -- q is a query type variable
  q QUERY(a INT, b VARCHAR(30)) = SELECT 1, "red";
BEGIN
  IF rand() > 0.5 THEN
    RETURN q;
  END IF;
  RETURN x;
END //
DELIMITER ;

```

## Assignment

You may assign a value to a query type variable in the declaration of the variable using `=`, or `:=`. The value assigned may be a `SELECT` statement, a query variable, or a query parameter. You may assign a value to a query variable in the body of a procedure using `=` or `:=`.

## Query Type Example

The following example demonstrates how to pass a query as an input parameter to a procedure, assign a query result from a stored procedure into a query type variable, and conditionally assign different values into a query type variable.

```sql
DELIMITER //
CREATE OR REPLACE PROCEDURE p(
  x QUERY(a INT, b VARCHAR(30)),
  a INT)
RETURNS QUERY(a INT, b VARCHAR(30)) AS
DECLARE
  q QUERY(a INT, b VARCHAR(30)) = SELECT 1, "purple";
BEGIN
  IF a = 0 THEN
    RETURN q;
  ELSE
    RETURN x;
  END IF;
END //

CREATE OR REPLACE PROCEDURE q(i INT)
RETURNS QUERY(a INT, b VARCHAR(30)) AS
DECLARE
  v QUERY(a INT, b VARCHAR(30)) = SELECT 2, "yellow";
  l QUERY(a INT, b VARCHAR(30)) = SELECT null, null;
BEGIN
  l = p(v, i);
  RETURN l;
END //
DELIMITER ;

ECHO q(0);

+------+--------+
| a    | b      |
+------+--------+
|    1 | purple |
+------+--------+

ECHO q(1);

+------+--------+
| a    | b      |
+------+--------+
|    2 | yellow |
+------+--------+

```

## Binding Variables to a Query Type Value

Query variable and parameter values may include both a query statement as well as bindings of variables. Both scalar variable types and `QUERY` variable types can be bound. For example:

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

DELIMITER //
CREATE OR REPLACE PROCEDURE p2(n INT)
  RETURNS QUERY(a INT, b VARCHAR(30)) AS
DECLARE q QUERY(a INT, b VARCHAR(30)) = SELECT a, b FROM t WHERE t.a >= n;
BEGIN
  RETURN q;
END //
DELIMITER ;

```

```sql
ECHO p2(1);

```

```output

+------+-------+
| a    | b     |
+------+-------+
|    3 | green |
|    2 | blue  |
|    1 | red   |
+------+-------+
```

```sql
ECHO p2(2);

```

```output

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

In the first call to `p2`, the query returned is

```sql
SELECT a, b FROM t WHERE t.a >= 1

```

which produces 3 rows of output. In the second call to `p2`, the query returned is

```sql
SELECT a, b FROM t WHERE t.a >= 2

```

which produces 2 rows of output. The variable `a` at the time of the call is bound into the query prior to returning the query.

## Late Binding

PSQL performs *late binding* of queries assigned to query type variables or returned by stored procedures. This means that the results of the query are not produced until they are needed due to an `ECHO` call, or by use of the functions `SCALAR` or `COLLECT`.

## Capture Semantics

`QUERY` type variables use the original value of each stored procedure variable they reference. This means that the value used within the body of the `QUERY` will be the value that the variable had at `QUERY` type variable creation time, not `QUERY` type variable execution time.

## Using Query Type Values In Queries

You can use query type values in other queries similar to the way you would reference a view. A query type variable or parameter name can be used in another query in a stored procedure. The query type value will be expanded inline into the enclosing query, the same way a view is expanded.

For example, the stored procedure defined below declares query type variable `q` and uses it in an `INSERT ... SELECT ...` statement to add data to the table called `target`.

```sql
CREATE TABLE source(a int, b int);
CREATE TABLE target(a int, b int);
INSERT INTO source VALUES(1, 2), (2, 2), (3, 2), (4, 3), (5, 3);

DELIMITER //

CREATE OR REPLACE PROCEDURE p() AS
DECLARE
    q QUERY(a int, b int) = SELECT a, b FROM source WHERE b = 2;
BEGIN
    INSERT INTO target SELECT a, b from q;
END //

DELIMITER ;

CALL p();
SELECT * FROM target;

```

```output

+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
|    2 |    2 |
|    3 |    2 |
+------+------+

```

In the example above, notice that the query type variable `q` is used in the `FROM` clause. You can also apply filters, joins, etc. to QTVs in a query, similar to a view. For example,

```sql
DELIMITER //
CREATE OR REPLACE PROCEDURE p() AS
DECLARE
    q QUERY(a int, b int) = SELECT a, b FROM source;
BEGIN
    ECHO SELECT * FROM q WHERE a < 3 ORDER BY a ASC;
END //
DELIMITER ;

call p();

```

```output

+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
|    2 |    2 |
+------+------+

```

You can also assign values to query type variables in the body of a stored procedure, instead of the DECLARE block. For example,

```sql
CREATE TABLE t(a int, b int);

INSERT t VALUES(1,2),(3,4),(5,6);

DELIMITER //

CREATE OR REPLACE PROCEDURE ptest(x INT) AS
DECLARE
  q QUERY(a INT, b INT);
BEGIN
  IF x < 0 THEN
    q = SELECT a, b FROM t;
  ELSE
    q = SELECT a, b FROM t WHERE a = x;
  END IF;
  echo SELECT a, b FROM q;
END //

DELIMITER ;

CALL ptest(1);

```

```output

+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
+------+------+

```

```sql
CALL ptest(-1);

```

```output

+------+------+
| a    | b    |
+------+------+
|    3 |    4 |
|    5 |    6 |
|    1 |    2 |
+------+------+

```

## Database Binding Behavior for QTVs Within a Stored Procedure

If a database is not specified in a QTV within a stored procedure, the query uses the database that the stored procedure was defined in.

The following example explains this behavior.

```sql
CREATE DATABASE db_test1;
CREATE DATABASE db_test2;
CREATE DATABASE db_test3;

CREATE TABLE db_test1.tmp(val INT);
INSERT INTO db_test1.tmp VALUES (1),(2);

CREATE TABLE db_test2.tmp(val INT);
INSERT INTO db_test2.tmp VALUES (3),(4);

CREATE TABLE db_test3.tmp(val INT);
INSERT INTO db_test3.tmp VALUES (5),(6);

DELIMITER //

CREATE OR REPLACE PROCEDURE db_test3.sp() AS
DECLARE
  q QUERY(val INT) = SELECT * FROM tmp;
  p QUERY(val INT) = SELECT SUM(val) FROM db_test1.tmp;
BEGIN
  ECHO SELECT * FROM q;
  ECHO SELECT * FROM p;
END //

DELIMITER ;

```

Change the context database to `db_test1` and call the stored procedure.

```sql
USE db_test1;
CALL db_test3.sp();

```

```output

+------+
| val  |
+------+
|    5 |
|    6 |
+------+
2 rows in set (0.01 sec)

+------+
| val  |
+------+
|    3 |
+------+
1 row in set (0.01 sec)

```

Now, change the context database to `db_test2` and call the stored procedure.

```sql
USE db_test2;
CALL db_test3.sp();

```

```output

+------+
| val  |
+------+
|    5 |
|    6 |
+------+
2 rows in set (0.01 sec)

+------+
| val  |
+------+
|    3 |
+------+
1 row in set (0.28 sec)

```

Instead of using the currently selected database (specified with `USE`), the query `SELECT * FROM tmp;` uses the definer’s database (the database specified while defining the stored procedure).

**Related Topics**

* [COLLECT](https://docs.singlestore.com/cloud/reference/sql-reference/procedural-sql-reference/collect.md): Executes a query and returns the result as an array of records.
* [ECHO](https://docs.singlestore.com/cloud/reference/sql-reference/procedural-sql-reference/echo.md): Executes a stored procedure and outputs a set of rows as a result.
* [SCALAR](https://docs.singlestore.com/cloud/reference/sql-reference/procedural-sql-reference/scalar.md): Executes a query that returns a single value.
* [TO\_QUERY](https://docs.singlestore.com/cloud/reference/sql-reference/procedural-sql-reference/to-query.md): Converts a SQL string to a query type value.

***

Modified at: June 12, 2023

Source: [/cloud/reference/sql-reference/procedural-sql-reference/query/](https://docs.singlestore.com/cloud/reference/sql-reference/procedural-sql-reference/query/)

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