How To: Stored Procedure In MySQL (4 Min) | Create, Drop Stored Procedures | Learn If, Else

In this tutorial, you'll learn how to use Stored Procedures in MySQL using Create Procedure, Drop Procedure and If, Else block.
-
-
Video Transcript:
—
—
Hi guys, this is Abhi from Gokcedb. In this video, you're going to learn how to Create a Stored Procedure in the MySQL database. Let's start by looking at the entity relationship diagram.

I have seven tables in my database right now but in this tutorial, we'll be working on the property and the property underscores status table. Let's run a select query to see what's in the property underscore status table. As you can see, we have four statuses sold on a cash, sold on terms, available and listed available, and unlisted.

The property table has a property underscore status underscore ID column which is a foreign key and references the ID column in the property underscore status table. Say we want to update the status of a property. The first thing we need to do is run a select query to see whether the property actually exists in the table.

Here, we can see that property name 1 exists and has an ID of 396 and the status ID is 4. Suppose we want to update the status to 3. To do that, use the update table set syntax with a where clause.

Looks like it went through. Now, let's see how we can accomplish the same thing using the store process. Let's open the SQL file to look at the code.

Start by setting the delimiter to dollar dollar so it doesn't interfere with the semicolon which is the delimiter for regular SQL statements. Next, I'm using the create procedure procedure name syntax with the parameters in parentheses. The in keyword means that the information is passed into the stored procedure.

The first parameter is the name of the property and the second parameter is the new status ID. The meat of the procedure code is written between the beginning and the end markers. Use the declare keyword to declare a variable followed by the data type and use the into keyword to store the output of the select query into the temp variable.

Note, in the where clause, were using the user underscore property underscore name parameter which was passed by the user. Next, we have the if-else block if the temp ID is null then say no ID found, else say success ID found and run the update statement. Use the end if keyword to specify the end of the if block followed by n dollar dollar specifying the end of the procedure.

We're also going to set the delimiter back to a semicolon. Now, I'll be using the MyQSL Alias where I'm passing the authentication details using the config file. Let's input our create procedure SQL file into the MySQL Alias to verify that the procedure was successfully created.

Use the show procedure status command. To look at the procedure definition, use the show create procedure procedure name syntax. And to execute a procedure use the call process name followed by the parameters in parentheses syntax.

Say I want to update the status of property names 1 to four. To do that, I'll pass 1 as the first parameter and four as the second parameter. Looks like it went through.

To verify, let's run a select query now. Say I want to flip the status back to 2. All I have to do is change the second parameter to 2.

Watch what happens when I pass an invalid property name as the first parameter, I get an error message saying the ID doesn't exist and the update statement was never executed. Finally, delete a stored procedure using the drop procedure procedure name syntax. There you have it.

Make sure you like, subscribe, and turn on the notification bell. Until next time.