SQL: Beginner Guide to Store Procedures (Part 2/2)
a thread...
a thread...
Before reading further please go through the Part-1:
In Part-2, we will see different types of parameters in SP.
And there characteristics, let see them one by one.
And there characteristics, let see them one by one.
1. Parameters in Store Procedure
Three types of Parameters in SP:
- IN Parameter
- OUT Parameter
- INOUT Parameter
Let's see each one of them in detail.
Three types of Parameters in SP:
- IN Parameter
- OUT Parameter
- INOUT Parameter
Let's see each one of them in detail.
2. SP with IN Parameter
When the caller only wants to send arguments in SP then IN parameter can be used.
SP never modifies the original IN parameter but it always works on its copy and only modifies the copy.
Eg: Get employee SP with IN Parameter
When the caller only wants to send arguments in SP then IN parameter can be used.
SP never modifies the original IN parameter but it always works on its copy and only modifies the copy.
Eg: Get employee SP with IN Parameter
Let's call this SP and see the output.
In Output, we will notice that although the value of the "id" parameter changed inside SP, when accessed from outside we will still see the original value, not the modified value.
Eg:
In Output, we will notice that although the value of the "id" parameter changed inside SP, when accessed from outside we will still see the original value, not the modified value.
Eg:
3. SP with OUT Parameter
It is defined using "OUT" keyword
SP sends the output to the caller via OUT param.
The initial value of the OUT parameter is always NULL in SP.
SP can modify the original OUT param value.
Eg: Get employee SP with OUT param.
It is defined using "OUT" keyword
SP sends the output to the caller via OUT param.
The initial value of the OUT parameter is always NULL in SP.
SP can modify the original OUT param value.
Eg: Get employee SP with OUT param.
As shown below, an initial value of id=101 is not accessible inside SP. Its initial value is NULL.
From NULL, "id" changed to 102 and it fetched the respective result of EMP_ID=102.
The "id" with value 102 is sent as output by SP.
Eg:
From NULL, "id" changed to 102 and it fetched the respective result of EMP_ID=102.
The "id" with value 102 is sent as output by SP.
Eg:
4. SP with INOUT param
It's a mix of both IN and OUT param.
The value passed by the caller can be received inside SP, and the value modified by SP is also received as output by SP.
Eg: Get employee SP with INOUT param
It's a mix of both IN and OUT param.
The value passed by the caller can be received inside SP, and the value modified by SP is also received as output by SP.
Eg: Get employee SP with INOUT param
As shown, the initial value of id=101 is received in SP as it is.
The original value of "id" is changed to 103 and the query fetched results of EMP_ID=103.
The modified value of id=103 is received as output from SP.
Eg:
The original value of "id" is changed to 103 and the query fetched results of EMP_ID=103.
The modified value of id=103 is received as output from SP.
Eg:
5. Conclusion
We've seen all three types of parameters in SP. We can have all three parameters at the same time inside SP.
Based on the requirement we can choose which type of parameter we need to use.
We've seen all three types of parameters in SP. We can have all three parameters at the same time inside SP.
Based on the requirement we can choose which type of parameter we need to use.
That's it for now!
If you like this then you might also like reading my previous thread.
If you like this then you might also like reading my previous thread.
Namaste, I'm Vikas!
Every Mon, Wed & Fri - I write a thread
on Fullstack Development.
Follow @vikasrajputin to read all my future threads.
Every Mon, Wed & Fri - I write a thread
on Fullstack Development.
Follow @vikasrajputin to read all my future threads.
جاري تحميل الاقتراحات...