Re: Stored procedure vs. function
"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