User Comments

This is very useful for exiting a stored procedure. (BTW this is not new, I found it today in several places and decided to add it here)

create procedure spMySproc(IN iParam INT)
sproc:BEGIN
if iParam < 0 then
leave sproc;
end if;
END

Note: If you get an error after adding the label, like with "start", pick a different word and see if the error goes away.

thank You for your comment, it was usefull for me, because intially a have not found a way to exit from MySQL procedure on some conditions.
But Your code is a little bit incorrect - this would be better ( at least Your code does not work on 5.0.18-nt ):
--------
create procedure spMySproc(IN iParam INT)
BEGIN
sproc:BEGIN
if iParam < 0 then
leave sproc;
end if;
END; # sproc: END;
END;

Keywords: LEAVE LABEL

I think this part of the manual is not very well explained. There isn't an example for the command and I couldn't find a specify section in the manual talking about LABELS. I hope this could be helpful // Pienso que esta parte del manual no esta muy bien explicada. No se detallan ejemplos de uso del comando y tampoco pude encontrar una seccion especifica sobre etiquetas (LABELS). Espero que esto pueda ser de ayuda:

Example 1: (PROCEDURE)

|------------------------------------------
|  mysql> DELIMITER //
|  mysql> CREATE PROCEDURE a(IN vShow TINYINT(1))
|      -> label:BEGIN
|      -> IF NOT vShow THEN LEAVE label; END IF;
|      -> SELECT "You can see this text" AS "Message";
|      -> END;//
|  Query OK, 0 rows affected (0.00 sec)
|
|  mysql> CALL a(TRUE);//
|  +-----------------------+
|  | Message               |
|  +-----------------------+
|  | You can see this text |
|  +-----------------------+
|  1 row in set (0.00 sec)
|
|  mysql> CALL a(FALSE);//
|  Query OK, 0 rows affected (0.00 sec)
|------------------------------------------

|------------------------------------------
|  mysql> DELIMITER //
|  mysql> CREATE PROCEDURE a(IN vLoop TINYINT(1))
|      -> BEGIN
|      -> SET @a:=1;
|      -> label:WHILE @a<10 DO
|      -> SELECT @a AS "Loop";
|      -> IF @a>=vLoop THEN LEAVE label; END IF;
|      -> SET @a:=@a+1;
|      -> END WHILE;
|      -> END;//
|  Query OK, 0 rows affected (0.00 sec)
|
|  mysql> CALL a(2);//
|  +------+
|  | Loop |
|  +------+
|  | 1    |
|  +------+
|  1 row in set (0.00 sec)
|
|  +------+
|  | Loop |
|  +------+
|  | 2    |
|  +------+
|  1 row in set (0.00 sec)
|
|  Query OK, 0 rows affected (0.00 sec)
|------------------------------------------

Add your own comment.