# Using Compound Statements Outside of Stored Programs

Compound statements can also be used outside of [stored programs](https://mariadb.com/docs/server/server-usage/stored-routines).

```sql
delimiter |
IF @have_innodb THEN
  CREATE TABLE IF NOT EXISTS innodb_index_stats (
    database_name    VARCHAR(64) NOT NULL,
    table_name       VARCHAR(64) NOT NULL,
    index_name       VARCHAR(64) NOT NULL,
    last_update      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    stat_name        VARCHAR(64) NOT NULL,
    stat_value       BIGINT UNSIGNED NOT NULL,
    sample_size      BIGINT UNSIGNED,
    stat_description VARCHAR(1024) NOT NULL,
    PRIMARY KEY (database_name, table_name, index_name, stat_name)
  ) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;
END IF|
Query OK, 0 rows affected, 2 warnings (0.00 sec)
```

Using compound statements this way is subject to following limitations:

* Only [BEGIN](https://mariadb.com/docs/server/reference/sql-statements/programmatic-compound-statements/begin-end), [IF](https://mariadb.com/docs/server/reference/sql-statements/programmatic-compound-statements/if), [CASE](https://mariadb.com/docs/server/reference/sql-statements/programmatic-compound-statements/case-statement), [LOOP](https://mariadb.com/docs/server/reference/sql-statements/programmatic-compound-statements/loop), [WHILE](https://mariadb.com/docs/server/reference/sql-statements/programmatic-compound-statements/while), [REPEAT](https://mariadb.com/docs/server/reference/sql-statements/programmatic-compound-statements/repeat-loop) statements may start a compound statement outside of stored programs.
* [BEGIN](https://mariadb.com/docs/server/reference/sql-statements/programmatic-compound-statements/begin-end) must use the `BEGIN NOT ATOMIC` syntax (otherwise it'll be confused with [BEGIN](https://mariadb.com/docs/server/reference/sql-statements/transactions/start-transaction) that starts a transaction).
* A compound statement might not start with a label.
* A compound statement is parsed completely—note "2 warnings" in the above example, even if the condition was false (InnoDB was, indeed, disabled), and the [CREATE TABLE](https://mariadb.com/docs/server/server-usage/tables/create-table) statement was not executed, it was still parsed and the parser produced "Unknown storage engine" warning.

Inside a compound block first three limitations do not apply, one can use anything that can be used inside a stored program — including labels, condition handlers, variables, and so on:

```sql
BEGIN NOT ATOMIC
    DECLARE foo CONDITION FOR 1146;
    DECLARE x INT DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR SET x=1;
    INSERT INTO test.t1 VALUES ("hndlr1", val, 2);
    END|
```

Example how to use `IF`:

```sql
IF (1>0) THEN BEGIN NOT ATOMIC SELECT 1; END ; END IF;;
```

Example of how to use `WHILE` loop:

```sql
DELIMITER |
BEGIN NOT ATOMIC
    DECLARE x INT DEFAULT 0;
    WHILE x <= 10 DO
        SET x = x + 1;
        SELECT x;
    END WHILE;
END|
DELIMITER ;
```

<sub>*This page is licensed: CC BY-SA / Gnu FDL*</sub>

{% @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/using-compound-statements-outside-of-stored-programs.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.