154

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;
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Joe Ijam
  • 2,123
  • 4
  • 19
  • 30
  • 1
    Or, you could use `IF tablename IS NOT NULL THEN` ... ;) – OMG Ponies Jun 07 '11 at 02:50
  • 5
    I am trying to fine short cut... otherwise i have to code inside the IF statement, and this is not the only EXIT statement... that y i need exit function instead we do multiple IF inside Stored Proc. – Joe Ijam Jun 07 '11 at 03:07
  • Good reference URL: https://bytes.com/topic/mysql/answers/829197-how-use-return-keyword-mysql-stored-procedure – Avishek Feb 24 '18 at 18:08

7 Answers7

247
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;
piotrm
  • 12,038
  • 4
  • 31
  • 28
  • 1
    Great! You even point out that the `END proc_label;` syntax (shown in most official MySQL examples) is not needed. (this is a great way to comment out a stored proc without having to scroll to the bottom to put `*/` in place) –  May 22 '13 at 14:36
  • 2
    can you leave and return a value? – ygaradon Apr 08 '14 at 10:28
  • 42
    Just label the BEGIN section of every proc 'this_proc'. Because `LEAVE this_proc;` sounds perfect! – SNag Jun 01 '14 at 19:03
  • @ygaradon Stored *procedures* do not return values. You need to use a stored *function* and `return ` to return a value. – David Harkness Aug 11 '14 at 20:42
  • @SNag, Exactly, `create procedure f()f:begin leave f; end` would do fine. – Pacerier Apr 01 '15 at 07:41
  • 1
    @David Harkness can't he pass it to an out parameter ? Isn't that returning a value ? – Ken Sep 11 '16 at 23:09
  • 1
    I think space is necessary between `:` and `BEGIN` as `proc_label:BEGIN` gave syntax error while `proc_label: BEGIN` worked. – Umair Malhi Aug 09 '17 at 10:06
  • Must labels be unique?, What happens if inside the `#proceed the code` I call another **store procedure** which uses the same label for leaving itself?. Will it leave itself, or will it leave the **parent store procedure**? – Madacol Feb 12 '20 at 16:52
22

If you want an "early exit" 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 <Some Error Condition> THEN      
    SIGNAL CUSTOM_EXCEPTION
    SET MESSAGE_TEXT = 'Your Custom Error Message';
END IF;     

Note SQLSTATE '45000' equates to "Unhandled user-defined exception condition". 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!

BuvinJ
  • 10,221
  • 5
  • 83
  • 96
  • 1
    This feels like the most correct, thorough answer to me and was exactly what i wanted. Like the OP, i have several tests (input validation) i need to run and i didn't want to nest them all , so this works well for me. – Fodagus Nov 28 '17 at 00:47
15

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;
Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • 8
    Yucks, why not use the first solution instead? – Pacerier Apr 01 '15 at 07:42
  • 2
    Wish I could vote this up twice. Just because SQL isn't a real programming language doesn't give anyone an excuse to write 200+ lines of code in a single procedure. – Max Heiber Jul 31 '15 at 18:22
  • Is this answer just plain wrong or am I missing something? Why does it have upvotes? Clearly there is a way to achieve this which is demonstrated by the accepted solution. – jlh Nov 21 '18 at 14:46
  • @jlh it *was* wrong (text corrected now) in that I didn’t know about mysql’s label technique, but the code isn’t wrong - it will work, on any DB actually. – Bohemian Nov 21 '18 at 14:53
4

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
2

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;
Stephen
  • 18,827
  • 9
  • 60
  • 98
  • 8
    The code is very long... i cannot use this... That is just a sample. – Joe Ijam Jun 07 '11 at 02:53
  • No matter the length, it will not execute. – Stephen Jun 07 '11 at 02:54
  • If you're worried about indenting, just unindent the entire section in the `if` statement. It's logically identical to an "early return". – bobobobo Jun 06 '13 at 19:38
  • @bobobobo, He's saying in his case it makes logically **much** more sense not tot rewire the logic around this sql limitation. – Pacerier Apr 01 '15 at 07:43
  • 1
    May be he has a login with lots of checks "if x IS NULL THEN SETresult = -1". You want it to REALLY stop doing things. It reduces the complexity of ifs. Less {} annidated – borjab Apr 21 '15 at 17:00
1
MainLabel:BEGIN

IF (<condition>) 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;
sdfor
  • 6,324
  • 13
  • 51
  • 61
0

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);
user1587368
  • 314
  • 2
  • 6