Text copied!
Store Procedure vs. Function
In SQL, both store procedure and function can be defined as a block/set of SQL statements that are used to perform a specific task.
Key differences between stored procedure and function :
1. Objective :
• Store procedure is used to retrieve or manipulate data and to perform some business logic.
• Function is used to perform some calculation or transformation and it always returns a value(s).
2. Return type :
• Store procedure doesn't necessarily require a 'return type', therefore it is not necessary to return a value.
• Function must have a 'return type' to return a value(s).
3. Error handling :
• Store procedure can handle errors using 'TRY-CATCH blocks'. 'TRY-CATCH blocks' is used to handle errors within a block/set of SQL statements.
• Function can't include 'TRY-CATCH blocks'.
4. Implementation in queries :
• Store procedure can't be used directly within the SELECT statement, WHERE clause or JOIN clause.
• Function can be used directly within the SELECT statement, WHERE, HAVING or JOIN clause.
5. Transaction handling :
• Store procedure can control transactions by using transaction control statement like 'BEGIN TRANSACTION', 'COMMIT' or 'ROLLBACK'.
• Function can't contain transaction control statements.
6. Invoke each other :
• Store procedure can't be invoked or called within a function.
• Function can be invoked or called within a stored procedure.
While deciding between store procedure or function, It is important to examine specific requirements. Developers has to consider various factor and evaluate specific requirements carefully before making a choice between store procedure and function.