MySQL Nodejs – Calling Stored Procedure

In this tutorial, we will study how to call stored procedures in the MySQL database server from the Nodejs application.

Stored Procedures

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.

SYNTAX:

CREATE PROCEDURE name_of_the_procedure
AS
sql_command
GO;

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)

Next, we will write a javascript file named stored.js which will call the stored procedure detail and return the result set. The code for the following is-

var mysql = require(‘mysql’); var connection = mysql.createConnection({ host: ‘localhost’, user: ‘root’, password: ‘*****’, database: ‘app’ }); let sql = `CALL detail(?)`;connection.query(sql, true, (error, results, fields) => { if (error) { return console.error(error.message); } console.log(results[0]); });connection.end();

Now write the following command in the command prompt to get the result-

node stored.jsCode language: CSS (css)

Output-

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.

Conclusion

In this tutorial, we studied how to call stored procedure in MySQL database server from Nodejs application.