Skip navigation links
MySQL 5.1 Reference Manual :: 12 SQL Statement Syntax :: 12.7 MySQL Compound-Statement Syntax :: 12.7.1 BEGIN ... END Compound Statement Syntax
12.7.2 DECLARE Syntax »
Section Navigation      [Toggle]

12.7.1. BEGIN ... END Compound Statement Syntax

[begin_label:] BEGIN
    [statement_list]
END [end_label]

BEGIN ... END syntax is used for writing compound statements, which can appear within stored programs. 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 (;) statement delimiter. statement_list is optional, which means that the empty compound statement (BEGIN END) is legal.

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 with the delimiter command. Changing the ; end-of-statement delimiter (for example, to //) allows ; to be used in a program body. For an example, see Section 19.1, “Defining Stored Programs”.

A BEGIN ... END block can be labeled. Labels follow these rules:

  • end_label cannot be given unless begin_label is also present.

  • If both begin_label and end_label are present, they must be the same.

  • Labels can be up to 16 characters long.

Labels are also allowed for the LOOP, REPEAT, and WHILE statements.

The optional [NOT] ATOMIC clause is not supported. This means that no transactional savepoint is set at the start of the instruction block and the BEGIN clause used in this context has no effect on the current transaction.


User Comments

Posted by Sujith Kumar John.G. on September 7 2006 1:21pm[Delete] [Edit]

The variable1 is one, the condition "if variable1 = 0" isn't true. Therefore everything between the IF and the END IF gets skipped.

CREATE PROCEDURE p12 (IN parameter1 INT)
BEGIN DECLARE variable1 INT;
SET variable1 = parameter1 + 1;
IF variable1 = 0 THEN INSERT INTO t VALUES (17);
END IF;
IF parameter1 = 0 THEN UPDATE t SET s1 = s1 + 1;
ELSE UPDATE t SET s1 = s1 + 2;
END IF;

Add your own comment.