In this tutorial, we will study how to call stored procedures in the MySQL database server from the Nodejs application.
An SQL-prepared code that may be saved and reused repeatedly is known as a stored procedure. So, if you frequently develop SQL queries, save them as stored procedures and just call them to run them. Additionally, you can send parameters to a stored procedure, allowing it to take action based on the value(s) of the passed parameters.
CREATE PROCEDURE name_of_the_procedure
Calling Stored Procedure
STEPS OF CALLING STORED PROCEDURES:
- Connect to MySQL Database server.
- Now call the stored procedure using CALL procedure statement, where procedure is the name of the stored procedure.
- Lastly, close the database connection.
Examples of Calling Stored Procedures With NodeJs
First of all, we will create a stored procedure named detail which will query data from the table customer based on the values of the completed field. Let us take a look at the customer table.
The completed field had value in the form of true or false and that is why 1 and 0 are depicted there respectively.
Now let us create a stored procedure named detail.
Delimiter $$ Create Procedure `detail`(In done Boolean) Begin Select * from customer Where completed = done; End$$ Delimiter;Code language: SQL (Structured Query Language) (sql)
Now write the following command in the command prompt to get the result-
node stored.jsCode language: CSS (css)
In the CALL statement, we can see that a placeholder (?) is used to pass the data to the stored procedure. So here, the completed value that is 1 is present in the result set and hence, the stored procedure is working fine.
In this tutorial, we studied how to call stored procedure in MySQL database server from Nodejs application.