The MySQL syntax for stored procedures

In this lecture, we will focus on the SQL syntax, allowing us to create stored procedures. Before we begin, remember that we will apply all procedures and functions to the employees database. Therefore, to avoid any confusion while typing the code in this section, let's write an execute. Use employees semicolon. Perfect. We are ready to move on. Think of how semicolons are used in SQL. We said they function as a statement terminator, but technically they can also be called limiters. And by typing Delimiter and the dollar symbol two times you'll be able to use the dollar symbols as your delimiter. The semicolon isn't your delimiter anymore. And why would you do that? Well, think of the long sheets of code you can have in the SQL editor there. Every query is terminated by a semicolon. Right. Now imagine you are invoking or calling a certain procedure that uses the semicolon as a delimiter. In that case, the SQL engine will run only the first of the statements in your procedure, and we'll move on to the next query that is beyond the procedure. It is not going to read the code after the first semicolon. To avoid this problem, you need a temporary delimiter different from the standard semicolon. There are various symbols. You can use a double dollar sign or a double forward slash, for instance. It doesn't really matter which one you choose. I will opt for the double dollar symbol. Great. We must then write, create procedure, and attach the name we would like to assign to it. Next to the name. Remember that you must always open and close parentheses. They are inherent to the syntax for creating a procedure because within these parentheses you would typically insert parameters. What do parameters do? They represent certain values that the procedure will use to complete the calculation it is supposed to execute. This concept is more intuitive than you might think at first. To clarify it in this section, we have prepared a few videos showing how to use procedures with parameters. For the moment, though, please remember that a procedure can be created without parameters to. Nevertheless, the parentheses must always be attached to its name. Otherwise, my SQL will display an error message. Clear. What follows is the body of the procedure. It is always enclosed between the keyword begin, the keyword end and the temporary delimiter, which in our case is a double dollar sign. My SQL workbench will display a black vertical line along the left side of the body of the procedure with a tiny box on top. By clicking on the minus or the plus sign located within this box, you can hide. Or expand the code of the body. You see? Fantastic. Please pay attention to the following. The body of the procedure is composed of a query, and this query is the reason you are creating the entire procedure in the first place. It will be placed between the begin and end keywords. More importantly, however, at the end of this query, we'll have the usual delimiter, the semicolon, not the double dollar sign. How come? Well, if we use the delimiter again, the creation of the procedure will stop here and my SQL will show an error. Excellent. Finally, do not forget to reset the delimiter to the classical semicolon symbol. If you forget to do that, you risk making the opposite mistake, not run any of the code, succeeding the line where you are calling the procedure. And from this moment on, the double dollar sign will not act as a delimiter. Once again, the semicolon will have this rule. Wonderful. I hope you enjoyed this video. Next, we will learn how to create a procedure and invoke it. Thank you for watching.