Blog

MySql Stored Funcitons and Procedures

Difference between a stored function and a stored procedure:

  • a procedure is run with a CALL statement where a function can be used directly inside a SQL statement.
  • inside a function one can use trivial SQL statements but inside a procedure not.
  • classic: a function can return a single value but a procedure can have INOUT values, where function params are all only IN type.
  • RETURNS and RETURN statement should be present in a function definition to define the type and value being returned, but in a procedure definition they are not allowed.
  • In Stored procedures dynamic SQL can be used but not in functions or triggers.

NOTE: 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.

The syntax can be found here.

comments powered by Disqus