In the previous segment, you were introduced to a UDF, which is a type of a stored routine. There is another type of stored routine in SQL: a stored procedure. This is similar to a UDF but differs in certain ways. Watch the next video to learn more about stored procedures.
As you learnt in this video, the syntax for writing a stored procedure is as follows:
In the next video, you will see a demonstration of how stored procedures are used in SQL. Note that this is a simple example, but stored procedures can become quite complex according to the business use case.
In this video, you learnt how to use stored procedures in SQL. Although the syntax of a stored procedure is similar to that of UDFs, there are many differences between the two. In the next video, you will learn about these differences in detail.
The differences between UDFs and stored procedures are summarised in the table given below.
UDF | Stored Procedure |
It supports only the input parameter, not the output. | It supports input, output and input-output parameters. |
It cannot call a stored procedure. | It can call a UDF. |
It can be called using any SELECT statement. | It can be called using only a CALL statement. |
It must return a value. | It need not return a value. |
Only the ‘select’ operation is allowed. | All database operations are allowed. |
Keep in mind the above differences whenever you need to determine the right kind of stored routine to use. Now that you have learnt all about stored routines, you will learn about another concept which is used in advanced applications of SQL, which is the cursor.