asked on

MySQL Equivalent of 'return' in a stored procedure

Hi, I am in the process of converting an MSSQL DB over to MySQL and have run into a problem with my stored procedures.

I often put logic into my stored procedures for data validation etc and if something passed in is not valid then I stop the execution of the stored procedure by using the 'return' command in MSSQL

i.e.

-- validate input
if (@input = 'bad')
begin
return
end

-- do something else
insert into some_table values (...)

However I cannot find an equivalent way of stopping the execution of the stored proc in MySQL as 'return' is not allowed. Some of these procs get quite long so I dont want to add a load of nested if statements.

Does anyone know a way of doing this in MySQL?

Thanks

Guy Hengel [angelIII / a3]

yes, mysql has the return statement:
http://dev.mysql.com/doc/refman/5.1/en/return.html

ishando

How about

-- validate input
if (@input <> 'bad')
begin
-- do something else
insert into some_table values (...)
end

Guy Hengel [angelIII / a3]

I see the error: you need to write like this:

if (_input = 'bad') then
  return;
end if;

Open in new window

mijk

ASKER

MySQL says that it is an incomplete statement and wont accept the stored proc going into MySQL.

See example below

create procedure do_something(_input varchar (64))
begin
 
  if (_input = 'bad') then
    return;
  end if;
 
  -- do something else....
 
end;

Open in new window

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mijk

ASKER

Thanks for the feedback guys,

I have just found this on the MySQL forum.

Using the 'leave' keyword and a label type thingy.

create procedure do_something(_input varchar (64))
begin
 
mainproc:
  begin
 
    if (_input = 'bad') then
      leave mainproc;
    end if;
 
  -- do something else....
 
  end;
 
end;

Open in new window