Mysql - How to quit exit from stored procedure

Question

I have very simple question but i did t get any simple code to exit from SP using Mysql  Can anyone share with me how to do that   CREATE PROCEDURE SP Reporting IN tablename VARCHAR 20   BEGIN      IF tablename IS NULL THEN            Exit this stored procedure here      END IF         proceed the code END

User · Answer

I think this solution is handy if you can test the value of the error field later  This is also applicable by creating a temporary table and returning a list of errors  DROP PROCEDURE IF EXISTS  procName  DELIMITER    CREATE PROCEDURE  procName  params  BEGIN     DECLARE error INT DEFAULT 0      DECLARE CONTINUE HANDLER FOR NOT FOUND SET error   1      SELECT         fields     FROM  tables     WHERE  where     ORDER BY  sorting LIMIT 1     INTO  vars      IF error   0 THEN        SELECT  vars      ELSE        SELECT 1 AS error         SET  error   0      END IF  END   CALL  procName  effp

User · Answer

This works for me     CREATE DEFINER  root      PROCEDURE  save package as template   IN package id int    IN bus fun temp id int    OUT o message VARCHAR  50                OUT o number INT    BEGIN  DECLARE  v pkg name  varchar 50     DECLARE  v pkg temp id  int 10       DECLARE  v workflow count INT 10    -- checking if workflow created for package select count     INTO v workflow count from workflow w where w package id    package id    this proc BEGIN   -- this proc block start here    IF  v workflow count   0 THEN    select  no work flow   as  workflow status        SET o message   Work flow is not created for this package        SET  o number   -2         LEAVE this proc   END IF   select  work flow  created   as  workflow status    -- To  send some message SET o message   SUCCESSFUL   SET  o number   1      END  -- this proc block end here   END

User · Answer

Why not this   CREATE PROCEDURE SP Reporting IN tablename VARCHAR 20   BEGIN      IF tablename IS NOT NULL THEN            proceed the code      END IF         Do nothing otherwise END

User · Answer

MainLabel BEGIN  IF   lt condition gt   IS NOT NULL THEN     LEAVE MainLabel  END IF        code  i e  IF   skipMe  IS NOT NULL THEN     skipMe returns Null if never set or set to NULL         LEAVE MainLabel  END IF

User · Answer

To handle this situation in a portable way  ie will work on all databases because it doesn   t use MySQL label Kung fu   break the procedure up into logic parts  like this   CREATE PROCEDURE SP Reporting IN tablename VARCHAR 20   BEGIN      IF tablename IS NOT NULL THEN          CALL SP Reporting 2 tablename        END IF  END   CREATE PROCEDURE SP Reporting 2 IN tablename VARCHAR 20   BEGIN       proceed with code END

User · Answer

CREATE PROCEDURE SP Reporting IN tablename VARCHAR 20   proc label BEGIN      IF tablename IS NULL THEN           LEAVE proc label       END IF         proceed the code END

User · Answer

If you want an  quot early exit quot  for a situation in which there was no error  then use the accepted answer posted by  piotrm   Most typically  however  you will be bailing due to an error condition  especially in a SQL procedure   As of MySQL v5 5 you can throw an exception  Negating exception handlers  etc  that will achieve the same result  but in a cleaner  more precise manner  Here s how  DECLARE CUSTOM EXCEPTION CONDITION FOR SQLSTATE  45000    IF  lt Some Error Condition gt  THEN           SIGNAL CUSTOM EXCEPTION     SET MESSAGE TEXT    Your Custom Error Message   END IF        Note SQLSTATE  45000  equates to  quot Unhandled user-defined exception condition quot    By default  this will produce an error code of 1644  which has that same meaning    Note that you can throw other condition codes or error codes if you want  plus additional details for exception handling   For more on this subject  check out  https   dev mysql com doc refman 5 5 en signal html How to raise an error within a MySQL function http   www databasejournal com features mysql mysql-error-handling-using-the-signal-and-resignal-statements html Addendum As I m re-reading this post of mine  I realized I had something additional to add   Prior to MySQL v5 5  there was a way to emulate throwing an exception   It s not the same thing exactly  but this was the analogue  Create an error via calling a procedure which does not exist   Call the procedure by a name which is meaningful in order to get a useful means by which to determine what the problem was  When the error occurs  you ll get to see the line of failure  depending on your execution context   For example  CALL AttemptedToInsertSomethingInvalid  Note that when you create a procedure  there is no validation performed on such things   So while in something like a compiled language  you could never call a function that wasn t there  in a script like this it will simply fail at runtime  which is exactly what is desired in this case

[mysql] Mysql - How to quit/exit from stored procedure

Examples related to mysql

Examples related to sql

Examples related to stored-procedures