How do i run a stored procedure in mysql workbench with parameters?
I have an Insert stored procedure where I am inserting into 2 tables. The second table using the Last_Insert_ID of the first table. Here is my sproc: Show
Here is how I am testing the query in MySql workbench:
There error I get is: "Column Address1 cannot be null" Where am I going wronng? Is it in the sproc? Or the way I am calling it? In this article, we are going to learn about the stored procedures in MySQL. In this article, I am covering the basics of the stored procedure that includes the following
The stored procedure is SQL statements wrapped within the CREATE PROCEDURE statement. The stored procedure may contain a conditional statement like IF or CASE or the Loops. The stored procedure can also execute another stored procedure or a function that modularizes the code. Following are the benefits of a stored procedure:
The syntax to create a MySQL Stored procedure is the following: Create Procedure [Procedure Name] ([Parameter 1],
[Parameter 2], [Parameter 3] ) In the syntax:
To execute the store procedure, you can use the CALL keyword. Below is syntax: CALL [Procedure Name] ([Parameters]..) In the syntax:
Let us create a basic stored procedure. For demonstration, I am using the sakila database. Create a simple stored procedureSuppose you want to populate the list of films. The output should contain film_id, title, description, release year, and rating column. The code of the procedure is the following:
To create the MySQL Stored Procedure, open the MySQL workbench Connect to the MySQL Database copy-paste the code in the query editor window click on Execute.
You can view the procedure under stored procedures. See the below screenshot.
To execute the procedure, run the below command. Below is the partial screenshot of the output:
Crete procedure using MySQL workbench wizardWe can use the MySQL workbench wizard to create a stored procedure. Suppose you want to get the list of the customer from the sakila database. To do that, expand the sakila schema Right-click on Stored Procedures Select Create a Stored procedure.
In the New procedure window, a create template has been created. In the template, replace the procedure name with sp_getCustomers. In the code block, enter the following query
Click on Apply. A dialog box, Apply script to database opens. On the Review the script screen, you can view the code of the stored procedure. Click on Apply.
The script is applied successfully, and a stored procedure sp_GetCustomer has been created successfully.
In MySQL Workbench, You can view the stored procedure under the Stored Procedures folder of the sakila schema.
Create a parameterized stored procedureThe MySQL Stored procedure parameter has three modes: IN, OUT, and INOUT. When we declare an IN type parameter, the application must pass an argument to the stored procedure. It is a default mode. The OUT type parameter, the stored procedure returns a final output generated by SQL Statements. When we declare the INOUT type parameter, the application has to pass an argument, and based on the input argument; the procedure returns the output to the application. When we create a stored procedure, the parameters must be specified within the parenthesis. The syntax is following:
In the syntax:
Example of IN parameterSuppose we want to get the list of films based on the rating. The param_rating is an input parameter, and the data type is varchar. The code of the procedure is the following:
To populate the list of the films with an NC-17 rating, we pass the NC-17 value to the sp_getMoviesByRating() procedure.
Output:
Example of OUT parameterSuppose we want to get the count of the films that have a PG-13 rating. The Total_Movies is an output parameter, and the data type is an integer. The count of the movies is assigned to the OUT variable (Total_Movies) using the INTO keyword. The code of the procedure is the following:
To store the value returned by the procedure, pass a session variable named @PGRatingMovies.
Example of an INOUT parameterSuppose we want to get the total count of movies based on the rating. The input parameter is param_rating in the procedure, and the data type is varchar(10). The output parameter is Movies_count, and the data type is an integer. Code of procedure:
Execute the procedure using CALL keyword and save the output in session variable named @MoviesCount
View the list of stored procedure in a database using a queryTo view the list of the stored procedure, you can query the information_schema.routines table. It contains the list of the stored procedure and stored functions created on the database. To view the list of the stored procedure created in a sakila database, run the following query. Moreover, it also provides the owner, created date, security type, and SQL data access to the stored procedures.
Drop a Stored ProcedureTo drop the stored procedure, you can use the drop procedure command. The syntax is following Drop procedure [IF EXISTS] In the syntax, the name of the stored procedure must be followed by the Drop Procedure keyword. If you want to drop the sp_getCustomers procedure from the sakila database, you can run the following query.
When you try to drop the procedure that does not exist on a database, the query shows an error: ERROR 1305 (42000): PROCEDURE sakila.getCustomer does not exist To avoid this, you can include the [IF EXISTS] option in the drop procedure command. When you include the IF EXISTS keyword, instead of an error, the query returns a warning: Query OK, 0 rows affected, 1 warning (0.01 sec) 1305 PROCEDURE sakila.getCustomer does not exist Drop a Stored Procedure using MySQL workbench wizardYou can use the MySQL workbench wizard to drop the procedure. To drop any procedure, expand sakila schema Expand Stored Procedures Right-click on sp_GetMovies Click on Drop Stored Procedure.
A dialog box opens. You can choose to review the procedure before dropping it, or you can drop it without reviewing it. It is good practice to review the database object before dropping it, so choose Review SQL.
In Review SQL Code to Execute dialog box, you can review the drop statement and the object name.
Click on Execute. The procedure will be dropped successfully. SummaryIn this article, we learned the basics of MySQL Stored procedure. I have covered following topics:
In next article, we are going to learn about the MySQL Views. Table of contents
Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on How do I call a procedure with parameters in MySQL?This procedure accepts id of the customer as IN parameter and returns product name (String), customer name (String) and, price (int) values as OUT parameters from the sales table. To call the procedure with parameters pass @parameter_name as parameters, in these parameters the output values are stored.
How do you call a stored procedure with parameters?Execute a stored procedure. Parameter. Indicates the name of the parameter.. Data Type. Indicates the data type of the parameter.. Output Parameter. Indicates if this is an output parameter.. Pass Null Value. Pass a NULL as the value of the parameter.. Value. Type the value for the parameter when calling the procedure.. Can a stored procedure use parameters?Parameters are used to exchange data between stored procedures and functions and the application or tool that called the stored procedure or function: Input parameters allow the caller to pass a data value to the stored procedure or function.
How do I execute a procedure in MySQL?Create a simple stored procedure. DELIMITER ; To create the MySQL Stored Procedure, open the MySQL workbench Connect to the MySQL Database copy-paste the code in the query editor window click on Execute. You can view the procedure under stored procedures.
|