# SET

Sets a value for a user-defined variable for use in SQL statements. This is analogous to the `SELECT ... INTO` user defined variables discussed here: [SELECT ... INTO User Defined Variable](https://docs.singlestore.com/db/v9.1/reference/sql-reference/user-defined-variables/select-into-user-defined-variable.md).

## Syntax

```
SET @my_var = <expr> [, @my_var_b = <expr_b>, @my_var_c = ...]

```

## Remarks

* The expression can be as simple as a single digit or string, or it can be the result of a SQL statement. Basically, anything that is valid in the form: `SELECT <expr> INTO @my_var;`
* The value assigned to the variable must be a single value (cannot assign multiple rows or columns).
* User-defined variables are case-insensitive, which means `@rollno` is the same as `@RollNo`.
* These variables are session-specific; they expire once the session ends. Also, a session variable defined by a user cannot be viewed or referenced by another session.
* The data type of a user-defined variable is inferred automatically.
* The `INFORMATION_SCHEMA.USER_VARIABLES` table contains a list of all the user-defined variables.

## Examples

Setting the variable's value via a basic expression:

```sql
SET @a = 5 + 5;

SELECT @a;

```

```output

+------+
| @a   |
+------+
|   10 |
+------+
1 row in set (0.054 sec)
```

Setting the value referencing another user defined variable:

```sql
SET @a = 5 + 5;

SET @b = @a * @a;

SELECT @b;

```

```output

+------+
| @b   |
+------+
|  100 |
+------+
1 row in set (0.051 sec)
```

Setting the value as the result of a SQL statement:

```sql
CREATE TABLE set_example (a int, b int);
INSERT INTO set_example VALUES(1,2),(3,4),(5,6),(7,8);

SELECT * FROM set_example;

```

```output

+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
|    3 |    4 |
|    5 |    6 |
|    7 |    8 |
+------+------+
4 rows in set (0.001 sec)

```

```sql
SET @a = (SELECT COUNT(*) FROM set_example);

SELECT @a;

```

```output

+------+
| @a   |
+------+
|    4 |
+------+
1 row in set (0.000 sec)
```

```sql
SET @a = (SELECT b FROM set_example WHERE a = 7);

SELECT @a;

```

```output

+------+
| @a   |
+------+
|    8 |
+------+
1 row in set (0.000 sec)
```

```sql
SET @a = (SELECT b,a FROM set_example WHERE a = 7);
ERROR 1241 (21000): Operand should contain 1 column(s)

SET @a = (SELECT b FROM set_example);
ERROR 1242 (21000): Subquery returns more than 1 row
```

User-defined variables on the right-hand side of assignments are bound before the `SET` statement is run.

```sql
SET @x = 1;

SET @y = 2;

SET @x = @y, @y = @x;

SELECT @x, @y;

```

```output

+----+----+
| @x | @y |
+----+----+
| 2  | 1  |
+----+----+

```

Other than that, the assignments in the list are run left-to-right.

```sql
SET @x = 1, @x = 2;

SELECT @x;

```

```output

+----+
| @x |
+----+
| 2  |
+----+
```

User defined variables and functions:

```sql
SET @a = 8;

DELIMITER //
USE db //

CREATE OR REPLACE FUNCTION foo (a INT) RETURNS INT AS BEGIN RETURN a * a; END //

DELIMITER ;

SET @c = foo(@a);

SELECT @a, @c;

```

```output

+------+------+
| @a   | @c   |
+------+------+
|    8 |    64|
+------+------+

```

```sql
SET @d = (SELECT @a) * 2;
Query OK, 0 rows affected (0.098 sec)

SELECT @d;

```

```output

+------+
| @d   |
+------+
|   16 |
+------+
1 row in set (0.055 sec)
```

```sql
SET @d = (SELECT @a) * 2, @e = foo(10), @f = @a + @b + @@SESSION.net_read_timeout;
Query OK, 0 rows affected (0.062 sec)

SELECT @d, @e, @f;

```

```output

+------+------+------+
| @d   | @e   | @f   |
+------+------+------+
|   16 |  100 | 3708 |
+------+------+------+
1 row in set (0.055 sec)
```

```sql
SELECT @@SESSION.net_read_timeout;

```

```output

+----------------------------+
| @@SESSION.net_read_timeout |
+----------------------------+
|                       3600 |
+----------------------------+
1 row in set (0.075 sec)
```

```sql
SET @g := @a + @b + @@SESSION.net_read_timeout;
Query OK, 0 rows affected (0.081 sec)

SELECT @g;

```

```output

+------+
| @g   |
+------+
| 3708 |
+------+
1 row in set (0.069 sec)
```

Using strings and objects:

```sql
SET @t = FROM_UNIXTIME(1234567890), @s = 'abc', @fp = 3.14;

SELECT @t, @s, @fp;

```

```output

+---------------------+-----+------+
| @t                  | @s  | @fp  |
+---------------------+-----+------+
| 2009-02-14 01:31:30 | abc | 3.14 |
+---------------------+-----+------+
1 row in set (0.053 sec)


```

```sql
SET @str = CONCAT(@s, @@GLOBAL.sql_mode);

SELECT @str;

```

```output

+-----------------------------------------------------+
| @str                                                |
+-----------------------------------------------------+
| abcANSI_QUOTES,ONLY_FULL_GROUP_BY,STRICT_ALL_TABLES |
+-----------------------------------------------------+
1 row in set (0.059 sec)
```

***

Modified at: April 25, 2025

Source: [/db/v9.1/reference/sql-reference/user-defined-variables/set/](https://docs.singlestore.com/db/v9.1/reference/sql-reference/user-defined-variables/set/)

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