# TO\_QUERY

Allows you to convert a [dynamic SQL](https://docs.singlestore.com/db/v9.1/developer-resources/procedural-extensions/dynamic-sql.md) statement to a query type value.

**Note:** Queries that are built from the `TO_QUERY()` function and query type values are parameterized when they are run, which allows plans to be reused in the plancache.

## Syntax

```sql
TO_QUERY ( select_statement )

```

## Arguments

**select\_statement**

A string containing a [SELECT](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/select.md) statement.

## Return Type

String

## Remarks

`TO_QUERY` may be used in an assignment statement where a query type variable is on the left side. It can also be used in a return statement of a function which returns a query type value, or as an argument to a function that accepts a query type value.

`TO_QUERY` and `COLLECT` are often used [together](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/collect.md) to specify and run dynamic queries.

## Examples

## Using `TO_QUERY` in an assignment statement

In the example below, following the assignment to the query type variable, the procedure `p` returns the value of the query type variable. This value is returned only so that its contents can be written to the console using the `ECHO` statement.

```sql
DROP DATABASE IF EXISTS singlestore_docs_example;
CREATE DATABASE singlestore_docs_example;
use singlestore_docs_example;
CREATE TABLE t1(color TEXT);
CREATE TABLE t2(color TEXT);

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

DELIMITER //
CREATE PROCEDURE p(tbl TEXT)
RETURNS QUERY(color TEXT) AS
DECLARE
  q QUERY(color TEXT) = TO_QUERY(CONCAT('SELECT color FROM ', tbl));
BEGIN
  RETURN q;
END
//
DELIMITER ;

ECHO p('t2');

```

```output

+--------+
| color  |
+--------+
| purple |
| orange |
| blue   |
+--------+

```

## Using `TO_QUERY` in a `RETURN` statement

The example below, the procedure `p` returns a query type value.

```sql
DROP DATABASE IF EXISTS singlestore_docs_example;
CREATE DATABASE singlestore_docs_example;
use singlestore_docs_example;
CREATE TABLE t1(color TEXT);
CREATE TABLE t2(color TEXT);

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

DELIMITER //
CREATE PROCEDURE p(tbl TEXT)
RETURNS QUERY(color TEXT) AS
BEGIN
  RETURN TO_QUERY(CONCAT('SELECT color FROM ', tbl));
END
//
DELIMITER ;

ECHO p('t1');

```

```output

+-------+
| color |
+-------+
| green |
| red   |
| blue  |
+-------+

```

> **📝 Note**: As a best practice, it is advisable to use `TO_QUERY` when the shape of query to be built (e.g. its table names, field names, and `WHERE` clause structure) will not be fully known until runtime.In scenarios where the structure of the query is known, use statements like```sql
> q = SELECT a, b FROM t WHERE t.x = variable;
>
> ```instead of```sql
> q = TO_QUERY(CONCAT("SELECT a, b FROM t WHERE t.x = ", variable));
>
> ```The former is easier to read, and it should execute faster because it spends less time parsing at runtime.

## Database Binding Behavior for `TO_QUERY()` QTVs Within a Stored Procedure

For `TO_QUERY` query type values called inside a stored procedure, if the database is not specified, database binding is done at runtime.

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.sp1() AS
DECLARE
  q QUERY(val INT) = TO_QUERY("SELECT * FROM tmp");
  p QUERY(val INT) = TO_QUERY("SELECT * FROM db_test1.tmp");
BEGIN
  ECHO SELECT * FROM q;
  ECHO SELECT * FROM p;
END //

DELIMITER ;

```

Observe the query behavior by switching between the context databases with the `USE` command.

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

```

```output

+------+
| val  |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.12 sec)

+------+
| val  |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.12 sec)

```

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

```

```output

+------+
| val  |
+------+
|    3 |
|    4 |
+------+
2 rows in set (0.12 sec)

+------+
| val  |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.12 sec)

```

The queries (where database is not specified inside the stored procedure) use the currently selected database, which means that database binding happened at runtime.

**Related Topics**

* [COLLECT](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/collect.md): Executes a query and returns the result as an array of records.
* [QUERY](https://docs.singlestore.com/db/v9.1/reference/sql-reference/procedural-sql-reference/query.md): A data type representing a `SELECT` statement.
* [SELECT](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/select.md): Retrieves data from a table.

***

Modified at: June 15, 2023

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

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