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 has more limitations   I m likely wrong  but it seems a stored procedure can do everything and more a stored function can  Why when would I use a procedure vs a function

User · Accepted Answer

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

User · Answer

The most general difference between procedures and functions is that they are invoked differently and for different purposes      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  A function is invoked within an expression and returns a single value directly to the caller to be used in the expression  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    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  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     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   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 Functions are normally used for computations where as  procedures are normally used for executing business logic  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 Functions can t use FLUSH statements whereas Stored procedures can do  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  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

User · Answer

Stored procedure can be called recursively but stored function can not

User · Answer

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

User · Answer

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

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

Examples related to mysql

Examples related to stored-procedures

Examples related to sql-function