SQL: What are User Defined Functions?
a thread...
(Part: 2)
a thread...
(Part: 2)
Before reading further please go through part 1 where I've explained the basics of functions and in-built functions:
1. User-Defined Functions
- It's a customized function created for our need, it optionally takes an argument, processes it and returns the output.
- It is reusable and can be used inline in query.
- It's a customized function created for our need, it optionally takes an argument, processes it and returns the output.
- It is reusable and can be used inline in query.
2. Create Function
- Using "CREATE" statement we can create a new function.
- Below, we've created function "GetEmployeeName" which takes "ID" as parameter and returns "EmployeeName".
Eg:
- Using "CREATE" statement we can create a new function.
- Below, we've created function "GetEmployeeName" which takes "ID" as parameter and returns "EmployeeName".
Eg:
3. Remove Function
- Use "DROP" command to remove the function
Eg:
- Use "DROP" command to remove the function
Eg:
4. Modify Function
- Many DB don't directly allow to modify the function using ALTER command.
- As a workaround, we need to DROP it and CREATE it with new definition.
Eg:
- Many DB don't directly allow to modify the function using ALTER command.
- As a workaround, we need to DROP it and CREATE it with new definition.
Eg:
5. Usage
- We can use the function inside the SELECT query as shown in the below code snippet.
- Below, we're fetching the employee name against the employee id for each row in the query.
Eg:
- We can use the function inside the SELECT query as shown in the below code snippet.
- Below, we're fetching the employee name against the employee id for each row in the query.
Eg:
6. Benefits of Function
- Reusability
- Can be used effectively in SELECT, WHERE, and CASE statements
- It's Easy to use as compared to Store Procedure and can be used easily within the SQL statements.
- Reusability
- Can be used effectively in SELECT, WHERE, and CASE statements
- It's Easy to use as compared to Store Procedure and can be used easily within the SQL statements.
That's it for now!
Wanna learn SQL the cool way?
Try out these SQL games👇
Wanna learn SQL the cool way?
Try out these SQL games👇
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.
جاري تحميل الاقتراحات...