469,338 Members | 8,160 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,338 developers. It's quick & easy.

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?
Jul 23 '05 #1
4 33344

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

Jul 23 '05 #2
-
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.

Jul 23 '05 #3

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

Jul 23 '05 #4
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
Jul 13 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Lauren Quantrell | last post: by
7 posts views Thread by Jeff Wang | last post: by
4 posts views Thread by laurenq uantrell | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.