This statement is used to exit the flow control construct that
has the given label. It can be used within
BEGIN ...
END or loop constructs
(LOOP,
REPEAT,
WHILE).
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;
Posted by Luciano Fantuzzi on April 15 2007 4:36am
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) |------------------------------------------
Example 2: (LOOP)
|------------------------------------------ | 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) |------------------------------------------
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
Example 2: (LOOP)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)
Add your own comment.