# **DEALLOCATE PREPARE**

## **Grammar Description**

The purpose of the `DEALLOCATE PREPARE` statement is to release precompiled statements generated using `PREPARE`. After releasing the precompiled statement, executing the precompiled statement again will result in an error. If too many precompiled statements are created and not released using the `DEALLOCATE PREPARE` statement, the system variable will enforce the precompiled statement upper limit `max_prepared_stmt_count` prompt.

## **Grammar Structure**

```text
{DEALLOCATE | DROP} PREPARE stmt_name
```

## **Parameter definition**

| Parameters | Description |
| ---- | ---- |
|stmt_name | Name of precompiled SQL statement |

## **Example**

```text
> CREATE TABLE numbers(pk INTEGER PRIMARY KEY, ui BIGINT UNSIGNED, si BIGINT);
> INSERT INTO numbers VALUES (0, 0, -9223372036854775808), (1, 18446744073709551615, 9223372036854775807);
> SET @si_min = -9223372036854775808;
> SET @si_max = 9223372036854775807;
> PREPARE s2 FROM 'SELECT * FROM numbers WHERE si=?';
Query OK, 0 rows affected (0.00 sec)

> EXECUTE s2 USING @si_min;
+------+-----------------------------------------------------------------------------------------------------------------------
| pk | ui | si |
+------+-----------------------------------------------------------------------------------------------------------------------
| 0 | 0 | -9223372036854775808 |
+------+-----------------------------------------------------------------------------------------------------------------------
1 row in set (0.01 sec)

> EXECUTE s2 USING @si_max;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| pk | ui | si |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | 18446744073709551615 | 9223372036854775807 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.01 sec)

> DEALLOCATE PREPARE s2;
Query OK, 0 rows affected (0.00 sec)
```