Difference between a stored procedure and user defined function in SQL

Background

Whenever we write and run a set of SQL statements together they can be categorized in one of the following - 

1. Stored procedures
2. User defined functions

Advantage?

A very common interview question. Why would we write a SQL stored procedure when we can do the same via JDBC calls. The most simple answer is to avoid network traffic. Instead of making multiple JDBC calls we just call the procedure and handle the business logic in it.

Consider a very simple scenario. You have to maintain records of the money other people owe you or the amount you owe to others. So you create a table with lets say column as name  and amoutDue. Every time you make or receive payment you will have to fetch the data, compare it with current payment and then to addition or subtraction accordingly. This will increase your network calls leading to high network traffic. So instead you call a procedure with name and amount (positive or negative) and let the procedure handle the logic. So in procedure we would have logic to select row for the name provided., depending on the amount alter the amount column for the entry. If the amount is o then there is no need to keep the record/row in the table. So delete it.

There are other benefits as well like transaction management and error handling that can be done at stored procedure level but the major benefit is less network traffic. Another use case in centralized maintained . You have to change only the common stored procedure and the business logic will get reflected in all dependent applications.

Difference between Stored procedure and user defined Functions

1. A function can return only one value which is mandatory where as a stored procedure can have multiple (0 or more) output parameters.
2. Function can have only input parameters where as a stored procedure can have multiple input/output parameters.
3. We can use select as well as DML statements (insert/update/delete) in stored procedures but only select statement in a function.
4. We can call a function from within a procedure but we cannot call a procedure from a function.
5. We can do Exception handling as well as transaction management in procedures but we cannot do the same in functions.
6. Functions can be used in SQL statements anywhere in the WHERE/HAVING/SELECT clause where as stored procedures cannot be.
7. You can directly call a procedure and exec myUser.myPackage.myProc(myArg1,,myArg2). Function you can use it either in a query or call from a procedure.

 Note :  Above are very generic points and may not applicable to the database you use. For example in case of Oracle PL/SQL you have most of the functionality in function that is outlined as not possible in above points.  So refer to the documentation of your database vendor for specific capabilities.

User defined Functions

 Function in a general SQL context can be either - 

1. System Defined
1. Scalar Functions (Eg. abs(), round(), upper(), lower() etc.)
2. Aggregate Functions(Eg. max(), min(), avg(), count() etc.) [generally used in group by clause]
3. Usage : SELECT *, MyUser.MyScalarFunction() FROM MyTable
2. User Defined
1. Scalar Function
•  Scalar function return single value due to actions perform by the function. 
• Function can return value of any data type.
• Usage : SELECT * FROM MyTableFunction()
2. Inline Table-Valued Function
•  These type of functions returns a table variable due to actions perform by function. 
• We can only use a single SELECT statement to return the value.
3. Multi-Statement Table-Valued Function
• These type of functions returns a custom declared table variable due to action perform by function. 
• Here we need to explicitly define the table schema to be returned. We can perform operations on this table to insert/update/delete and then return.

Note : You must have noticed by  now that in above User Defined -> Multi-Statement Table-Valued Function we are using insert, update and delete but in the point mentioned at the top we have said we can only use select statement. I had mentioned this in note immediately after those points and will stress it once again those points are generic one and will vary from vendor to vendor. Above user defined functions are specific to SQL server database.

To see actual SQL queries for procedures and functions in PL/SQL refer -

• Experimenting with Oracle 11g R2 database

Related Links

• What is the difference between function and procedure in PL/SQL?
• User-Defined Functions in Microsoft SQl server
• Different Types of SQL Server Function with Examples