# BEGIN END

## Syntax

```sql
[begin_label:] BEGIN [NOT ATOMIC]
    [statement_list]
END [end_label]
```

`NOT ATOMIC` is required when used outside of a [stored procedure](https://mariadb.com/docs/server/server-usage/stored-routines/stored-procedures). Inside stored procedures or within an anonymous block, `BEGIN` alone starts a new anonymous block.

## Description

`BEGIN ... END` syntax is used for writing compound statements. A compound statement can contain multiple statements, enclosed by the `BEGIN` and `END` keywords. statement\_list represents a list of one or more statements, each terminated by a semicolon (i.e., `;`) statement delimiter. statement\_list is\
optional, which means that the empty compound statement (`BEGIN END`) is legal.

Note that `END` will perform a commit. If you are running in [autocommit](https://mariadb.com/docs/server/server-management/variables-and-modes/server-system-variables#autocommit) mode, every statement will be committed separately. If you are not running in `autocommit` mode, you must execute a [COMMIT](https://mariadb.com/docs/server/reference/sql-statements/transactions/commit) or [ROLLBACK](https://mariadb.com/docs/server/reference/sql-statements/transactions/rollback) after `END` to get the database up to date.

Use of multiple statements requires that a client is able to send statement strings containing the statement delimiter. This is handled in the [mysql command-line client](https://mariadb.com/docs/server/clients-and-utilities/mariadb-client/mysql-command-line-client) with the [DELIMITER ](https://mariadb.com/docs/server/clients-and-utilities/mariadb-client/mariadb-command-line-client#delimiters)command.\
Changing the `;` end-of-statement delimiter (for example, to`//`) allows `;` to be used in a program body.

A compound statement within a [stored program](https://mariadb.com/docs/server/server-usage/stored-routines) can be [labeled](https://mariadb.com/docs/server/reference/sql-statements/programmatic-compound-statements/labels). `end_label` cannot be given unless `begin_label` also is present. If both are present, they must be the same.

`BEGIN ... END` constructs can be nested. Each block can define its own variables, a `CONDITION`, a `HANDLER` and a [CURSOR](https://mariadb.com/docs/server/reference/sql-statements/programmatic-compound-statements/programmatic-compound-statements-cursors), which don't exist in the outer blocks. The most local declarations override the outer objects which use the same name (see example below).

The declarations order is the following:

* [DECLARE local variables](https://mariadb.com/docs/server/reference/sql-statements/programmatic-compound-statements/declare-variable)
* [DECLARE CONDITIONs](https://mariadb.com/docs/server/reference/sql-statements/programmatic-compound-statements/declare-condition)
* [DECLARE CURSORs](https://mariadb.com/docs/server/reference/sql-statements/programmatic-compound-statements/programmatic-compound-statements-cursors/declare-cursor)
* [DECLARE HANDLERs](https://mariadb.com/docs/server/reference/sql-statements/programmatic-compound-statements/declare-handler)

Note that `DECLARE HANDLER` contains another `BEGIN ... END` construct.

Here is an example of a very simple, anonymous block:

```sql
BEGIN NOT ATOMIC
SET @a=1;
CREATE TABLE test.t1(a INT);
END|
```

Below is an example of nested blocks in a stored procedure:

```sql
CREATE PROCEDURE t( )
BEGIN
   DECLARE x TINYINT UNSIGNED DEFAULT 1;
   BEGIN
      DECLARE x CHAR(2) DEFAULT '02';
       DECLARE y TINYINT UNSIGNED DEFAULT 10;
       SELECT x, y;
   END;
   SELECT x;
END;
```

In this example, a [TINYINT](https://mariadb.com/docs/server/reference/data-types/numeric-data-types/tinyint) variable, `x` is declared in the outter block. But in the inner block `x` is re-declared as a [CHAR](https://mariadb.com/docs/server/reference/data-types/string-data-types/char) and an `y` variable is declared. The inner [SELECT](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/select) shows the "new" value of `x`, and the value of `y`. But when x is selected in the outer block, the "old" value is returned. The final [SELECT](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/select) doesn't try to read `y`, because it doesn't exist in that context.

## See Also

* [Using compound statements outside of stored programs](https://mariadb.com/docs/server/reference/sql-statements/programmatic-compound-statements/using-compound-statements-outside-of-stored-programs)
* [Changes in Oracle mode](https://app.gitbook.com/s/aEnK0ZXmUbJzqQrTjFyb/community-server/about/compatibility-and-differences/sql_modeoracle)

<sub>*This page is licensed: GPLv2, originally from*</sub> [<sub>*fill\_help\_tables.sql*</sub>](https://github.com/MariaDB/server/blob/main/scripts/fill_help_tables.sql)

{% @marketo/form formId="4316" %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://mariadb.com/docs/server/reference/sql-statements/programmatic-compound-statements/begin-end.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.