Welcome to Stored Procedures. After watching this video, you will be able to: Explain what a stored procedure is List the benefits of using stored procedures Describe how to create and use a stored procedure A stored procedure is a set of SQL statements that are stored and executed on the database server. So instead of sending multiple SQL statements from the client to server, you encapsulate them in a stored procedure on the server and send one statement from the client to execute them. You can write stored procedures in many different languages. For example, for Db2 on Cloud and DB2 you can write in SQL PL, PL/SQL, Java, C, or other languages. They can accept information as parameters, perform create, read, update, and delete (CRUD) operations, and return results to the client application. The benefits of stored procedures include: Reduction in network traffic because only one call is needed to execute multiple statements. Improvement in performance because the processing happens on the server where the data is stored, with just the final result being passed back to the client. Reuse of code because multiple applications can use the same stored procedure for the same job. Increase in security because a) you do not need to expose all of your table and column information to client-side developers and b) you can use server-side logic to validate data before accepting it into the system. Remember though, that SQL is not a fully-fledged programming language, so you should not try to write all of your business logic in your stored procedures. So let’s look how to create a stored procedure on Db2 on Cloud in SQL. Firstly, you use the CREATE PROCEDURE statement, specifying the name of the procedure and any parameters which it will take. In this example, the UPDATE_SAL procedure will take an employee number and a rating which it will use to update an employee’s salary by an amount depending on their rating. Then you declare the language you are using. You then enclose your procedural logic in the BEGIN END statements. In this case, giving employees who have a rating of 1 a 10% pay rise and all others, a 5% pay rise. Notice that you can use the information passed to the procedure, the parameters, directly in your procedural logic. You can call stored procedures from your external applications or from dynamic SQL statements. To call the UPDATE_SAL stored procedure that we just created, you use the CALL statement with the name of the stored procedure and pass the required parameters – in this case, the employee id and the rating for that employee. In this video, you learned that: Stored procedures are a set of SQL statements that execute on the server Stored procedures offer many benefits over sending SQL statements to the server You can use stored procedures in dynamic SQL statements and external applications