how to exit from stored procedure?

[-]

in my stored procedure i have some if else statements:
SET @status = "";

IF (code is invalid...
SET @status = "Invalid code.";
SELECT @status;
END IF;

IF (name is invalid..
SET @status = "Invalid name.";
SELECT @status
END IF;

IF (code and name already exists)
SET @status = "Record exists.";
SELECT @status
ELSE
SET @status = "Record added.";
SELECT @status
END IF;

SELECT @status;

The @status always return the value from the last IF .. ELSE statement.
Since RETURN can't be used in a procedure, what syntax should i use to
immediately exit the stored procedure if the first IF statement is true?

[Steve]

Just a guess here, but you are issuing SELECT @STATUS more than once.
That probably creates more than one returned recordset, and your client
is only seeing the first one.

---
Steve

[-]

Steve wrote:

Just a guess here, but you are issuing SELECT @STATUS more than once.
That probably creates more than one returned recordset, and your client
is only seeing the first one.

---
Steve

it's actually returning only one @status depending on the condition.

The SELECT @status in each if else statement is where the syntax is
wrong. Is there a way to exit the procedure immediately after each
SELECT? I noticed that other databases uses RETURN but mysql does not
allow RETURN in procedures.

[Steve]

There may not be any such language construct to do what you want to do,
so you will probably have to rearrange your logic.

I can only restate that your procedure ALWAYS issues at least two
SELECT @STATUS statements, each of which creates a resultset.

The first occurs in this code:

IF (code and name already exists)
SET @status = "Record exists.";
SELECT @status
ELSE
SET @status = "Record added.";
SELECT @status
END IF;

where one or other branch must be executed, and both have a SELECT
@STATUS, so one of those SELECT @STATUS statements will be executed.
The second occurs in this code:

SELECT @status;

which is an unconditional statement and will always be executed. Hence
a minimum of two SELECT @STATUS statements will be executed.

My advice would be to recode the conditional blocks to only SET the
value of @STATUS, not SELECT it. SELECT it unconditionally only once at
the end of the procedure. That way, you will definitely only get one
resultset back from the procedure call.

You might also experiment with a cheat using LOOP/END LOOP/LEAVE:

LOOP

IF cond
statements
LEAVE
END IF

IF cond
statements
LEAVE
END IF

LEAVE

END LOOP

---
Steve

[Thomas Johnson]

I had teh same issue last night. I found the easiset way is to add another Begin and End just inside the procedure using a label.

Create Procdure MyProc()
Begin
main: begin
{Procedure Logic Here....}
End main
End

Then anytime you want to exit use: leave main