In this tutorial in the beginning I'll show you how to create and use functions as well as stored procedures in SQL. Then I'll describe differences between them and tell you which one you should choose in specific situations.
Generally both are very useful to perform operations that are repetitive but there are few differences between them that you will discover in this tutorial.
Test data
Firstly if you want to check out how function and stored procedures by your own here you have scripts that were used in this tutorial.
To create a table employees use the following code:
To populate employees table use the following code:
The result will be the following table:
Stored Procedure
Stored procedures in SQL are used to perform some actions such as for example inserting new records to a table. It is simply a piece of code that can contain various SQL statements. You can pass parameters to stored procedure and there is also an option to retrieve parameter values from stored procedure.
In our example we'll create a stored procedure that will insert a new employee to employees table and then it will select all employees that are also working on a position that will be occupied by a new worker.
Take a look at the following code:
To call this procedure just use the following code:
As a result the new employee will be added to the table and employees which are working on the same position as the new one will be displayed (including the new one):
Now I'll show you how to retrieve values from stored procedure. To do this you have to use OUT keyword like in the following example:
To create a new variable and use this procedure to get average salary of all workers use the following code:
The result will be the following:
Summing up now you know how to create and call a procedure and how to use output parameters but generally instead of using output parameters it's better to use function to calculate average salary like in the example above. Now I'll show you how to create a function.
Functions
Functions are also grouped SQL statements however unlike in stored procedure you can't change database records in function using commands like update or insert. Functions on the other hand can return value that you can use later in other SQL statements so they are generally used for computing values. In SQL there are predefined functions such as for example count() which returns number of records or max() that returns the largest value from all records.
To create a function that will calculate average salary of all employees (like our previous stored procedure) let's use the following code:
Now to call this function execute the following code:
The result will be the average salary of all employees. What's important you can use functions in SQL statement like in the following example. We'll display only the employees whose salary is above the average:
The result will be the following:
Stored Procedure vs. Function
Now when you know how to use both stored procedure and function you probably also have an idea when to use which one but in this part I'll summarize the main differences between them.
In function you can't alter records from database, that means you can't execute commands such as insert, update or delete. You can however perform this operations in stored procedures so use it when you want to execute a set of SQL statements including the ones that alter data.
Unlike stored procedure function can be used inline in SQL statements, other functions or in procedures so use it when you need to compute some value that will be later used in some SQL statement.
Please note that both stored procedure as well as function can be used to achieve the same goal. Just keep in mind the rules that are above and you should be able to choose which one of these two mechanism suits you better in your current situation. It may come in handy in web design.