The MySQL syntax for stored procedures

A free video tutorial from 365 Careers
Creating opportunities for Data Science and Finance students
Rating: 4.5 out of 5Instructor rating
109 courses
2,755,699 students
The MySQL syntax for stored procedures

Lecture description

In this lecture, we will focus on the SQL syntax allowing us to create a specific type of a stored routine – the MySQL stored procedure. 

Learn more from the full course

SQL - MySQL for Data Analytics and Business Intelligence

SQL that will get you hired – SQL for Business Analysis, Marketing, and Data Management

11:14:18 of on-demand video • Updated February 2024

Become an expert in SQL
Learn how to code in SQL
Boost your resume by learning an in-demand skill
Create, design, and operate with SQL databases
Start using MySQL – the #1 Database Management System
Prepare for SQL developer, Database administrator, Business Analyst, and Business Intelligence job opportunities
Adopt professionally tested SQL best practices
Gain theoretical insights about relational databases
Work with a sophisticated real-life database throughout the course
Get maximum preparation for real-life database management
Add data analytical tools to your skillset
Develop business intuition while solving tasks with big data
Study relational database management theory that you will need in your workplace every day
Learn how to create a database from scratch
The ability to take control of your dataset – insert, update, and delete records from your database
Be confident while working with constraints and relating data tables
Become a proficient MySQL Workbench user
Acquire top-notch coding techniques and best practices
Know how to answer specific business questions by using SQL’s aggregate functions
Handle complex SQL joins with ease
Approach more advanced topics in programming like SQL’s triggers, sequences, local and global variables, indexes, and more
Merge coding skills and business acumen to solve complex analytical problems
Become a proficient SQL user by writing flawless and efficient queries
Tons of exercises that will solidify your knowledge
The freedom to query anything you like from a database
English [CC]
Instructor: 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 employee's database. Therefore, to avoid any confusion while typing the code in this section, let's write and 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 delimiters, 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 too. 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 role. Wonderful. I hope you enjoyed this video. Next, we will learn how to create a procedure and invoke it. Thank you for watch.