MySQL Forums
Forum List  »  Stored Procedures

Re: Stored procedure vs. function
Posted by: Andrew Gilfrin
Date: June 19, 2005 07:50AM

"but i want to know why are functions defined when SPs can do their work properly?"

They can't, for using in SQL statements the stored routine must return one and only one value. With procedures this isn't the case but with functions it will ALAWAYS return a single row and only ever a single row.

So for example...

create function AddA(p_inparam varchar(30)) returns varchar(30)
return concat('A',p_inparam);

mysql> select AddA(emp_name) from emps;
+----------------+
| AddA(emp_name) |
+----------------+
| ARoger |
| AJohn |
| AAlan |
+----------------+
3 rows in set (0.26 sec)

create procedure AddB(INOUT p_inparam varchar(30))
set p_inparam := concat('B',p_inparam);

mysql> select AddB(emp_name) from emps;
ERROR 1305 (42000): FUNCTION pers.AddB does not exist

mysql> select call AddB(emp_name) from emps;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'call AddB(emp_name) from emps' at line 1

Andrew Gilfrin
------------------
http://gilfster.blogspot.com
My MySQL related Blog

http://www.mysqldevelopment.com
MySQL Stored Procedure,Trigger, View.... (Just about most things these days) Information

Options: ReplyQuote


Subject
Views
Written By
Posted
25218
June 01, 2005 12:11AM
8170
June 01, 2005 03:53AM
5171
June 19, 2005 01:19AM
4478
June 19, 2005 05:28AM
Re: Stored procedure vs. function
19643
June 19, 2005 07:50AM


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.