MySQL stored procedure vs function, which would I use when ?

Question

I'm looking at MySQL stored procedures and function. What is the real difference ?

They seem to be similar, but a function have more limitations.

I'm likely wrong, but it seems a stored procedure can do everything and more an stored function can. Why/when would I use a procedure vs a function ?

Answer 1

A stored function can be used within a query. You could then apply it to every row, or within a WHERE clause.

A procedure is executed using the CALL query.

Answer 2

You can't mix in stored procedures with ordinary SQL, whilst with stored function you can.

e.g. SELECT get_foo(myColumn) FROM mytable is not valid if get_foo() is a procedure, but you can do that if get_foo() is a function. The price is that functions have more limitations than a procedure.

Answer 3

One significant difference is that you can include a function in your SQL queries, but stored procedures can only be invoked with the CALL statement:

UDF Example:

CREATE FUNCTION hello (s CHAR(20))
   RETURNS CHAR(50) DETERMINISTIC
   RETURN CONCAT('Hello, ',s,'!');
Query OK, 0 rows affected (0.00 sec)

CREATE TABLE names (id int, name varchar(20));
INSERT INTO names VALUES (1, 'Bob');
INSERT INTO names VALUES (2, 'John');
INSERT INTO names VALUES (3, 'Paul');

SELECT hello(name) FROM names;
+--------------+
| hello(name)  |
+--------------+
| Hello, Bob!  |
| Hello, John! |
| Hello, Paul! |
+--------------+
3 rows in set (0.00 sec)

Sproc Example:

delimiter //

CREATE PROCEDURE simpleproc (IN s CHAR(100))
BEGIN
   SELECT CONCAT('Hello, ', s, '!');
END//
Query OK, 0 rows affected (0.00 sec)

delimiter ;

CALL simpleproc('World');
+---------------------------+
| CONCAT('Hello, ', s, '!') |
+---------------------------+
| Hello, World!             |
+---------------------------+
1 row in set (0.00 sec)