As we dive into SQL, we often run into the dilemma of using Functions or Store Procedure.
The function has the following attributes:
- a set of SQL statements that accept only input parameters, perform actions and return the result.
- It is compiled and executed every time whenever it is called.
- It must return a value and cannot modify the data received as parameters
- It is not used to Insert, Update, Delete data in a database table
Where Store Procedure has the following attributes:
- It is pre-compiled objects which are compiled for the first time then is saved which enables to executes whenever it is called
- It can accept both input and output parameters
- It can return multiple values using output parameters
- It can perform Select, Insert, Update, Delete data in the database table
The thumb of the rule is using Function to get values or data from multiple tables and where SELECT statements are frequently used whereas Stored Procedures is helpful when performing a complex transaction, it can be further broken down into small chunk of Functions.
if you want to fast-track into a high-income skill via an alternative education provider Industry Connect. You can reach out to me to talk about high-income skills, Business Intelligence journey and life design in New Zealand/Australia.
Feel free to connect with me through Linkedin and see if I can answer your question.