Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 768
  • Last Modified:

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


0
mijk
Asked:
mijk
  • 3
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes, mysql has the return statement:
http://dev.mysql.com/doc/refman/5.1/en/return.html
0
 
ishandoCommented:
How about

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


0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I see the error: you need to write like this:
if (_input = 'bad') then
  return;
end if;

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
mijkAuthor Commented:
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

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I reread the docs:
http://dev.mysql.com/doc/refman/5.1/en/return.html

<...>The RETURN statement terminates execution of a stored function <...>

so indeed, they mean only stored function, and not stored procedure ... :(

in which case, you have to code like this:
create procedure do_something(_input varchar (64))
begin
 
  if (_input <> 'bad') then
 
  -- do something else....
  end if;
 
end;

Open in new window

0
 
mijkAuthor Commented:
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

0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now