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

by

The most general difference between procedures and functions is that they are invoked differently and for different purposes:

  1. A procedure does not return a value. Instead, it is invoked with a CALL statement to perform an operation such as modifying a table or processing retrieved records.
  2. A function is invoked within an expression and returns a single value directly to the caller to be used in the expression.
  3. You cannot invoke a function with a CALL statement, nor can you invoke a procedure in an expression.

Syntax for routine creation differs somewhat for procedures and functions:

  1. Procedure parameters can be defined as input-only, output-only, or both. This means that a procedure can pass values back to the caller by using output parameters. These values can be accessed in statements that follow the CALL statement. Functions have only input parameters. As a result, although both procedures and functions can have parameters, procedure parameter declaration differs from that for functions.

  2. Functions return value, so there must be a RETURNS clause in a function definition to indicate the data type of the return value. Also, there must be at least one RETURN statement within the function body to return a value to the caller. RETURNS and RETURN do not appear in procedure definitions.

    • To invoke a stored procedure, use the CALL statement. To invoke a stored function, refer to it in an expression. The function returns a value during expression evaluation.

    • A procedure is invoked using a CALL statement, and can only pass back values using output variables. A function can be called from inside a statement just like any other function (that is, by invoking the function’s name), and can return a scalar value.

    • Specifying a parameter as IN, OUT, or INOUT is valid only for a PROCEDURE. For a FUNCTION, parameters are always regarded as IN parameters.

    If no keyword is given before a parameter name, it is an IN parameter by default.
    Parameters for stored functions are not preceded by IN, OUT, or INOUT. All function parameters are treated as IN parameters.

To define a stored procedure or function, use CREATE PROCEDURE or CREATE FUNCTION respectively:

CREATE PROCEDURE proc_name ([parameters])
 [characteristics]
 routine_body


CREATE FUNCTION func_name ([parameters])
 RETURNS data_type       // diffrent
 [characteristics]
 routine_body

A MySQL extension for stored procedure (not functions) is that a procedure can generate a result set, or even multiple result sets, which the caller processes the same way as the result of a SELECT statement. However, the contents of such result sets cannot be used directly in expression.

Stored routines (referring to both stored procedures and stored functions) are associated with a particular database, just like tables or views. When you drop a database, any stored routines in the database are also dropped.

Stored procedures and functions do not share the same namespace. It is possible to have a procedure and a function with the same name in a database.

In Stored procedures dynamic SQL can be used but not in functions or triggers.

SQL prepared statements (PREPARE, EXECUTE, DEALLOCATE PREPARE) can be used in stored procedures, but not stored functions or triggers. Thus, stored functions and triggers cannot use Dynamic SQL (where you construct statements as strings and then execute them). (Dynamic SQL in MySQL stored routines)

Some more interesting differences between FUNCTION and STORED PROCEDURE:

  1. (This point is copied from a blogpost.)
    Stored procedure is precompiled execution plan where as functions are not. Function Parsed and compiled at runtime. Stored procedures, Stored as a pseudo-code in database i.e. compiled form.

  2. (I’m not sure for this point.)
    Stored procedure has the security and reduces the network
    traffic and also we can call stored procedure in any no. of
    applications at a time. reference

  3. Functions are normally used for computations where as
    procedures are normally used for executing business logic.

  4. Functions Cannot affect the state of database
    (Statements that do explicit or implicit commit or rollback are disallowed in function)
    Whereas
    Stored procedures Can affect the state of database using commit etc.
    refrence: J.1. Restrictions on Stored Routines and Triggers

  5. Functions can’t use FLUSH statements whereas Stored procedures can do.

  6. Stored functions cannot be recursive Whereas Stored procedures can be.
    Note: Recursive stored procedures are disabled by default, but can be enabled on the server by setting the max_sp_recursion_depth server system variable to a nonzero value. See Section 5.2.3, “System Variables”, for more information.

  7. Within a stored function or trigger, it is not permitted to modify a table that is already being used
    (for reading or writing) by the statement that invoked the function or trigger.
    Good Example: How to Update same table on deletion in MYSQL?

Note: that although some restrictions normally apply to stored functions and triggers but not to stored procedures, those restrictions do apply to stored procedures if they are invoked from within a stored function or trigger. For example, although you can use FLUSH in a stored procedure, such a stored procedure cannot be called from a stored function or trigger.

More Related Contents:

  1. Stored procedure that Automatically delete rows older than 7 days in MYSQL
  2. Can I create view with parameter in MySQL?
  3. Pass array to MySQL stored routine
  4. Connect By Prior Equivalent for MySQL
  5. How to raise an error within a MySQL function
  6. MySQL stored procedures use them or not to use them
  7. Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation ‘=’
  8. Looping Over Result Sets in MySQL
  9. mysql dynamic query in stored procedure
  10. MySQL : transaction within a stored procedure
  11. Dynamic table names in stored procedure function
  12. List of Stored Procedures/Functions Mysql Command Line
  13. passing LIMIT as parameters to MySQL sproc
  14. MySQL triggers cannot update rows in same table the trigger is assigned to. Suggested workaround?
  15. How do you debug MySQL stored procedures?
  16. How can I use transactions in my MySQL stored procedure?
  17. MySQL Stored Procedure Error Handling
  18. MySQL error #2014 – Commands out of sync; you can’t run this command now
  19. MySQL Stored Procedures not working with SELECT (basic question)
  20. use a variable for table name in mysql sproc
  21. Split a string and loop through values in MySQL stored procedure
  22. How to schedule a stored procedure in MySQL
  23. My SQL Dynamic query execute and get ouput into a variable in stored procedure
  24. Why MySQL is giving error “Not allowed to return a result set from a function”?
  25. MySQL : When stored procedure parameter name is the same as table column name
  26. SQL date format convert? [dd.mm.yy to YYYY-MM-DD]
  27. How do I write an SP in phpMyAdmin (MySQL)?
  28. Call a stored procedure for each row returned by a query in MySQL
  29. Mysql stored procedure don’t take table name as parameter
  30. How to pass a variable to a IN clause?

Leave a Comment