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!
I help people get started with Backend Development.
Follow me @vikasrajputin for more.
If you find this thread helpful then Like/Retweet the first tweet below:
I help people get started with Backend Development.
Follow me @vikasrajputin for more.
If you find this thread helpful then Like/Retweet the first tweet below:
Before you go, do you know?
I also write on LinkedIn!
I share some exclusive content there, which I never share here.
Follow me on Linkedin to never miss an important update on Backend content:
linkedin.com
I also write on LinkedIn!
I share some exclusive content there, which I never share here.
Follow me on Linkedin to never miss an important update on Backend content:
linkedin.com
**Correction**
Thanks Omkar for correcting me here:
Thanks Omkar for correcting me here:
جاري تحميل الاقتراحات...