MySQL Forums
Forum List  »  Stored Procedures

Re: user-define function cant work
Posted by: Andrew Gilfrin
Date: March 15, 2005 07:47AM

You can't currently use SQL within a Function this includes using cursors.

You will need to use a stored procedure and use an out parameter rather than using a return.

There's also no need to use the repeat as you not actually using the whole cursor just the first row. Either remove the repeat processing or a better solution might be to use select into, depends if you can be sure only a single row is returned.

You could amend you code as follows.

OPEN cur1;
FETCH cur1 INTO a;
CLOSE cur1;

This will do exactly the same as you have. The whole program written as a procedure could be as short as this.

create procedure GetCountry(IN phonenumber varchar(20), OUT param_country varchar(100)
begin

select country into param_country FROM test.Rates_MY WHERE Code = PhoneNumber;

end

Check out the documentation or look at my site which show's you how to return either a single or multiple rows from a cursor if the select into might return more than one row, it's at http://www.mysqldevelopment.com

Andrew Gilfrin
http://www.mysqldevelopment.com

Options: ReplyQuote


Subject
Views
Written By
Posted
4407
March 15, 2005 03:55AM
Re: user-define function cant work
2845
March 15, 2005 07:47AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.