SQL: Store Procedure vs Functions
🧵👇
🧵👇
1. Definition
1.a. What is Store Procedure:
1.a. What is Store Procedure:
1.b. What is Function:
2. Return Values
- SP may return multiple values but functions only return a single scalar value or a table.
- Functions always return a value while SP may or may not return a value.
- SP may return multiple values but functions only return a single scalar value or a table.
- Functions always return a value while SP may or may not return a value.
3. Parameters
- SP supports two types of Parameters Input & Output.
- Functions only supports Input Parameters.
- SP supports two types of Parameters Input & Output.
- Functions only supports Input Parameters.
4. Restrictions
- SP can Modify the DB state by using ALTER, UPDATE, DELETE, etc commands.
- Functions are restricted to use only SELECT statements, that's why they cannot change the DB state.
- SP can Modify the DB state by using ALTER, UPDATE, DELETE, etc commands.
- Functions are restricted to use only SELECT statements, that's why they cannot change the DB state.
5. Transaction
- SP allows the use of transactions, and we can write logic to roll back/commit transactions in it.
- Functions cannot make use of transactions.
- SP allows the use of transactions, and we can write logic to roll back/commit transactions in it.
- Functions cannot make use of transactions.
6. Exception Handling
- SP supports try-catch block, and exception handling code can be written inside it.
- Functions do not support it.
- SP supports try-catch block, and exception handling code can be written inside it.
- Functions do not support it.
7. Calling
- SP can call functions but functions cannot call SP.
- Functions can be called inline from SELECT, UPDATE, DELETE, and INSERT queries while SP can't be called like that.
- SP can call functions but functions cannot call SP.
- Functions can be called inline from SELECT, UPDATE, DELETE, and INSERT queries while SP can't be called like that.
That's a wrap!
If you loved reading this thread, you might enjoy reading my previous thread on SQL:
If you loved reading this thread, you might enjoy reading my previous thread on SQL:
Namaste, I'm Vikas!
Trying to diversify tech twitter with Back-end content.
Every Mon, Wed & Fri - I write a thread,
follow @vikasrajputin to read all my future threads.
Trying to diversify tech twitter with Back-end content.
Every Mon, Wed & Fri - I write a thread,
follow @vikasrajputin to read all my future threads.
جاري تحميل الاقتراحات...