{"id":5830,"date":"2017-06-15T20:44:14","date_gmt":"2017-06-16T03:44:14","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=5830"},"modified":"2024-01-03T18:16:57","modified_gmt":"2024-01-04T01:16:57","slug":"call-stored-procedures","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-nodejs\/call-stored-procedures\/","title":{"rendered":"Calling MySQL Stored Procedures from Node.js"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to call a stored procedure in MySQL from a Node.js application.<\/p>\n\n\n\n

This tutorial picks up where the <\/a>Deleting Data in MySQL from Node.js<\/a> tutorial left off.<\/p>\n\n\n\n

The steps for calling a stored procedure<\/a> are similar to the steps for executing a query<\/a> as follows:<\/p>\n\n\n\n

    \n
  1. Connect to the MySQL database server.<\/a><\/li>\n\n\n\n
  2. Call the stored procedure by executing the CALL spName<\/code> statement. The spName<\/code> is the name of the stored procedure.<\/li>\n\n\n\n
  3. Close the database connection.<\/li>\n<\/ol>\n\n\n\n

    Calling a MySQL stored procedure example<\/h2>\n\n\n\n

    For the demonstration, we create a new stored procedure<\/a> filterTodo<\/code> to query rows from the todos<\/code> table based on the value of the completed<\/code> field.<\/p>\n\n\n

    DELIMITER $$\n\nCREATE<\/span> PROCEDURE<\/span> filterTodo(IN<\/span> done BOOLEAN<\/span>)\nBEGIN<\/span>\n    SELECT<\/span> * FROM<\/span> todos WHERE<\/span> completed = done;\nEND<\/span>$$\n\nDELIMITER ;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    The stored procedure filterTodo<\/code> retrieves rows in the todos<\/code> table depending on the value of the done<\/code> argument. When the done<\/code> argument is true, it retrieves all completed todos; Otherwise, it returns the incomplete todos.<\/p>\n\n\n\n
    To call a stored procedure, you use the CALL<\/code> statement. For example, to call the filterTodo<\/code> stored procedure, you execute the following statement:<\/p>\n\n\n
    CALL<\/span> filterTodo(false<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    The statement returns the following result set:<\/p>\n\n\n
    +----+------------------------------------+-----------+\n| id | title                              | completed |\n+----+------------------------------------+-----------+\n|  2<\/span> | Insert a new<\/span> row with<\/span> placeholders |         0<\/span> |\n|  3<\/span> | Master Node.js MySQL               |         0<\/span> |\n+----+------------------------------------+-----------+\n2<\/span> rows in<\/span> set<\/span> (0.00 sec)\n\nQuery OK, 0 rows affected (0.00 sec)<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
    The following stored_procedure.js<\/code> program calls the filterTodo<\/code> stored procedure and returns the result set:<\/p>\n\n\n
    let<\/span> mysql = require<\/span>('mysql'<\/span>);\n\nlet<\/span> connection = mysql.createConnection({\n  host<\/span>: process.env.DB_HOST,\n  port<\/span>: process.env.DB_PORT,\n  user<\/span>: process.env.DB_USER,\n  password<\/span>: process.env.DB_PASSWORD,\n  database<\/span>: process.env.DB_NAME,\n});\n\nconnection.connect((err<\/span>) =><\/span> {\n  if<\/span> (err) return<\/span> console<\/span>.error(err.message);\n\n  let<\/span> sql = `CALL filterTodo(?)`<\/span>;\n\n  connection.query(sql, [false<\/span>], (error, results, fields) => {\n    if<\/span> (error) return<\/span> console<\/span>.error(error.message);\n\n    console<\/span>.log(results);\n  });\n\n  \/\/ close the database connection<\/span>\n  connection.end();\n});\n<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
    How it works.<\/p>\n\n\n\n
    First, import the mysql<\/code> module:<\/p>\n\n\n
    let<\/span> mysql = require<\/span>('mysql'<\/span>);<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
    Second, create a connection to the MySQL database using the createConnection<\/code> method:<\/p>\n\n\n
    let<\/span> connection = mysql.createConnection({\n  host<\/span>: process.env.DB_HOST,\n  port<\/span>: process.env.DB_PORT,\n  user<\/span>: process.env.DB_USER,\n  password<\/span>: process.env.DB_PASSWORD,\n  database<\/span>: process.env.DB_NAME,\n});<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
    Note that we retrieve the connection parameters (host, port, user, password, and database) from environment variables using .env<\/code>.<\/p>\n\n\n\n
    Third, connect to the MySQL database:<\/p>\n\n\n
    connection.connect((err<\/span>) =><\/span> {\n  if<\/span> (err) return<\/span> console<\/span>.error(err.message);\n});<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
    This code checks if there is any error and displays it.<\/p>\n\n\n\n
    Fourth, define an SQL query that calls the filterTodo<\/code> stored procedure:<\/p>\n\n\n
    let<\/span> sql = `CALL filterTodo(?)`<\/span>;<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
    Fifth, specify the data used in the query:<\/p>\n\n\n
    let<\/span> data = [false<\/span>];<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
    In this case, the placeholder ?<\/code> in the SQL query will be replaced by false<\/code>.<\/p>\n\n\n\n
    Sixth, execute the SQL query using the query<\/code> method:<\/p>\n\n\n
    connection.query(sql, [false<\/span>], (error, results, fields) => {\n  if<\/span> (error) return<\/span> console<\/span>.error(error.message);\n\n  console<\/span>.log(results);\n});<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
    This code executes the query and displays the number of rows affected.<\/p>\n\n\n\n
    Finally, close the database connection:<\/p>\n\n\n
    connection<\/span>.end<\/span>();<\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
    In this tutorial, you have learned how to call a stored procedure in MySQL from a Node.js program.<\/p>\n
    \n\t
    \n\t\t
    Was this tutorial helpful? <\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t