Function vs. Stored Procedure in SQL Server
Functions are computed values and cannot perform permanent environmental changes to SQL Server
(i.e., no INSERT
or UPDATE
statements allowed).
A function can be used inline in SQL
statements if it returns a scalar value or can be joined upon if it returns a result set.
A point worth noting from comments, which summarize the answer. Thanks to @Sean K Anderson:
Functions follow the computer-science definition in that they MUST return a value and cannot alter the data they receive as parameters
(the arguments). Functions are not allowed to change anything, must
have at least one parameter, and they must return a value. Stored
procs do not have to have a parameter, can change database objects,
and do not have to return a value.
MySQL stored procedure vs function, which would I use when?
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.
Functions vs Stored Procedures
If you're likely to want to combine the result of this piece of code with other tables, then obviously a table-valued function will allow you to compose the results in a single SELECT statement.
Generally, there's a hierarchy (View < TV Function < Stored Proc). You can do more in each one, but the ability to compose the outputs, and for the optimizer to get really involved decreases as the functionality increases.
So use whichever one minimally allows you to express your desired result.
What's the differences between stored procedures, functions and routines?
Google is your friend. The first match for "mysql routine function procedure" is this: http://dev.mysql.com/doc/refman/5.0/en/stored-routines-syntax.html
A quick summary:
A stored routine is either a procedure or a function.
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 and can return a scalar value.
Related Topics
Is Id Column Position in Postgresql Important
How to See the Structure of Mulitple Table with a Single "Desc"
Postgresql: Give All Permissions to a User on a Postgresql Database
How to List All the Columns in a Table
Search an Oracle Database for Tables with Specific Column Names
Two Single-Column Indexes VS One Two-Column Index in MySQL
How to Select a Record and Update It, with a Single Queryset in Django
Check Bound Datatable for Null Value Vb.Net
Calculate Difference Between 2 Dates in SQL, Excluding Weekend Days
Access Substitute for Except Clause
Difference Between Timestamps in Milliseconds in Oracle
How to Use Alias in Where Clause
How to Compare Dates in SQL Server
Oracle Trigger Error Ora-04091