# SCALAR

The `SCALAR` function executes a query that returns a single value.

## Syntax

```

SCALAR(query_type_value)

SCALAR(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/cloud/reference/sql-reference/procedural-sql-reference/query.md) value.

**field\_definition**

One `field_name` and one `data_type` must be specified.

**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**

Optional.

If not present or `NULL`, indicates that the one row, one column value can be `NULL`.

If `NOT NULL`, indicates that the one row, one column value cannot be `NULL`.

**select\_statement**

A string containing a `SELECT` statement that returns one row with one column.

## Remarks

The examples above use a `QUERY` with the field definition `a INT`. When used with `SCALAR`, the `QUERY` may only define one field, whose name doesn’t affect `SCALAR`’s behavior.

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

The procedure in the following example retrieves the maximum value from the single column table.

```sql


DROP DATABASE IF EXISTS singlestore_docs_example;
CREATE DATABASE singlestore_docs_example;
USE singlestore_docs_example;
CREATE TABLE t(a INT);
CREATE TABLE output_log(msg TEXT);

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

DELIMITER //
CREATE PROCEDURE p() AS
DECLARE
  q QUERY(a INT) = SELECT MAX(a) FROM t;
  v INT;
BEGIN
  v = SCALAR(q);
  INSERT INTO output_log VALUES (CONCAT('max value is ', v));
END
//
DELIMITER ;

CALL p();

SELECT * FROM output_log;

```

Output:

```

+----------------+
| msg            |
+----------------+
| max value is 5 |
+----------------+

```

## Example 2: Using `SCALAR` with Dynamic Queries

The following procedure finds the maximum value of a single column table, using a [dynamic query](https://docs.singlestore.com/cloud/developer-resources/procedural-extensions/dynamic-sql.md).

```sql


DROP DATABASE IF EXISTS singlestore_docs_example;
CREATE DATABASE singlestore_docs_example;
USE singlestore_docs_example;
CREATE TABLE t1(a INT);
CREATE TABLE t2(a INT);
CREATE TABLE output_log(msg TEXT);

INSERT INTO t1 VALUES (1), (2), (3);
INSERT INTO t2 VALUES (4), (5), (6);

DELIMITER //
CREATE PROCEDURE p(tbl VARCHAR(30)) AS
DECLARE
  v INT;
BEGIN
  v = SCALAR(CONCAT('SELECT MAX(a) FROM ', tbl), QUERY(a INT));
  INSERT INTO output_log VALUES (CONCAT('max value is ',v));
END
//
DELIMITER ;

CALL p('t1');

SELECT * FROM output_log;

```

Output:

```

+----------------+
| msg            |
+----------------+
| max value is 3 |
+----------------+

```

**Related Topics**

* [QUERY](https://docs.singlestore.com/cloud/reference/sql-reference/procedural-sql-reference/query.md): A data type representing a `SELECT` statement.

***

Modified at: April 24, 2021

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

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